Page 1 of 1

Select question

Posted: Sun Nov 10, 2002 5:56 pm
by tennis_blues
hi,

i have 2 tables:
Member
memberid int(6)
firstname varchar(20)
lastname varchar(20)

Grouping
groupid int(6)
memberid int(6)
visitor varchar(40)

the idea is that a grouping can have 1-n members or if someone isnt a member, then i just put the full name in the visitor column and a 0 in the memberid.....

so now my question is how do i select everyone in a grouping and get the full name out....

i tried
SELECT Member.FirstName, Member.LastName, Grouping.SubName FROM Member, Grouping WHERE 4 = Grouping.GroupID and Grouping.MemberID = Member.MemberID

but this is not getting the visitor names. can someone please point me in the right direction?

thanks so much in advance!

Posted: Mon Nov 11, 2002 1:33 am
by phpScott
try

Code: Select all

SELECT m.firstname, m.lastname FROM Member m, WHERE Grouping.groupid=4 AND m.memberid = Grouping.memberid
I think this is what you are looking for.

phpScott

Posted: Tue Nov 12, 2002 10:20 am
by Rob the R
I think you'll need to use a left join, since you'll want records in the GROUPING table that have no match in the MEMBER table (for visitors). I would suggest trying this:

Code: Select all

SELECT m.firstname, m.lastname, g.visitor
FROM grouping g LEFT JOIN member m USING (memberid)
WHERE g.groupid = 4
This will allow records to be returned from the GROUPING table even when there is no match on MEMBERID from the MEMBER table. If you want the names to be returned in the same column for members and visitors, you'll have to use the IFNULL function as well:

Code: Select all

SELECT IFNULL(g.visitor,CONCAT(m.firstname,' ',m.lastname)) AS fullname
FROM grouping g LEFT JOIN member m USING (memberid)
WHERE g.groupid = 4
This will return VISITOR if it has a value, and the concatenation of FIRSTNAME and LASTNAME otherwise.

I don't have MySQL in front of me, so forgive me if isn't exactly right.