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!
Select question
Moderator: General Moderators
try
I think this is what you are looking for.
phpScott
Code: Select all
SELECT m.firstname, m.lastname FROM Member m, WHERE Grouping.groupid=4 AND m.memberid = Grouping.memberidphpScott
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:
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:
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.
Code: Select all
SELECT m.firstname, m.lastname, g.visitor
FROM grouping g LEFT JOIN member m USING (memberid)
WHERE g.groupid = 4Code: 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 = 4I don't have MySQL in front of me, so forgive me if isn't exactly right.