Thanks for your help code green ,
I am one step closer with the following:
[code=mysql]
"select b.BookID, b.Title, b.Author, rl.DateFrom, rl.DateTo, c.Comment from book b left join responsibilityl og rl on b.BookID = rl.BookID left join bookcomment c on (b.BookID = c.BookID AND c.ContactID='"+ ContactID+"') WHERE (rl.ContactID=' "+ContactID +"' OR rl.ContactID IS NULL) OR (c.ContactID='" +ContactID+"'...
gertie2008
Last Activity: Jun 24 '08, 12:26 PM
Joined: Feb 15 '08
Location:
-
Thanks for you input Henry, you've given me some ideas to try out. I have tried various options concerning AND OR. The following example comes pretty close to what I want, except that it returns data from the bookcomment table where the ContactID is not equal to the one that I've specified. I suspect it is because of the way that the joins are made:
[code=mysql]select b.BookID, b.Title, b.Author, rl.DateFrom, rl.DateTo, c.Comment from...Leave a comment:
-
Thanks for your response,
I changed it to left join, but still:
The query only returns data if there is a record in bookcomment AND responsibilityl og that meet the conditions. I have data in responsibilityl og table that should be returned, but for that ContactID and BookID, there is no record in the bookcomment table and therefore it doesn't return the data from the responsibilityl og table.Leave a comment:
-
3 table join
Hi,
I've got 3 tables:
1.) book: BookID, Title
2.) bookcomment: BookCommentID, BookID, ContactID, Comment
3.) responsibilityl og: ResponsibilityI D, BookID, ContactID, DataFrom, DateTo
Relationships:
book and bookcomment: one to one
book and responsibilityl og: one to many
I want a query that returns book.Title and bookcomment.Com ment WHERE BookID=1 AND ContactID=2... -
Thanks for the guidance. I will give 2 examples:
1.)Values: ResponsiblePers onID:6 OwnerID:50 Username='Clem'
[code=mysql]
rs = st.executeQuery ("SELECT Title, Author, OwnerID, ResponsiblePers onID, Synopsys, if(ResponsibleP ersonID='0',(SE LECT Username FROM details WHERE ContactID=6),Ow nerID) as Myfield FROM book WHERE BookID=" + BookID);
[/code]
returns the following value: Myfield:50 (this works...Leave a comment:
-
If/else construct returns integer instead of varchar
I'm trying to get the 3rd query to return a varchar, but it returns an integer.
Query one to prove that a varchar can be returned:
[code=mysql]
SELECT IF(1=1,0,'one') , IF(1=0,0,'one') ;
+-----------------+-----------------+
| IF(1=1,0,"one") | IF(1=0,0,"one") |
+-----------------+-----------------+
| 0 | one |
+-----------------+-----------------+
[/c... -
Thanks for your help...Leave a comment:
-
This is what I came up with. Does anyone know a shorter way? (Thanks ronverdonk for helping me to post better.)...Leave a comment:
-
Problem solved:
...Code:CREATE DEFINER=`root`@`localhost` PROCEDURE `getSpecNotContacted`( IN CoachID INT, IN iCommType INT, IN fromDate DATE, IN toDate DATE ) BEGIN drop table IF EXISTS temp; CREATE TEMPORARY TABLE temp (CommTrackID INT, ContactID INT); INSERT INTO temp SELECT CommTrackID, ContactID FROM tblCommTrack WHERE DateContacted >= fromDate
Leave a comment:
-
Select ContactID if field doesn't have a specific value
Lets say I have 2 tables:
"tblContactDeta ils" with fields "ContactID" and "UserName" and
"tblCommTra ck" with fields "CommTrackI D", "ContactID" , "CommType", "DateContac ted"
Case one: tblCommTrack has one row with values: 1,10,1, 2008-03-13 respectively. The following query works, because it doesn't return any rows for the indivudual with ContactID=10... -
Select different columns based on specific date in different columns
Say I have a ContactDetails table with the following fields:
ContactID, ContactName, DateOfBirth, SpouseName, SpouseDateOfBir th, ChildName, ChildDateOfBirt h (and some other fields not worth mentioning)
I pass in 2 parameters: eg. fromDate='19001 111' and toDate='2007111 1'
If DateOfBirth is between fromDate and toDate I want to return ContactName and DateOfBirth
AND If SpouseDateOfBir th is between fromDate and toDate I...
No activity results to display
Leave a comment: