Select question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tennis_blues
Forum Newbie
Posts: 21
Joined: Sun Oct 20, 2002 8:30 am

Select question

Post 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!
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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
Rob the R
Forum Contributor
Posts: 128
Joined: Wed Nov 06, 2002 2:25 pm
Location: Houston

Post 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.
Post Reply