SQL select statement help

[Ranthalion]

Hi,

I have 2 tables that I'm interested in.

-----------------------------------------
Table: Members
Fields: id, name, prof1, prof2

Data: 1, Cheveyo, 1, 2
-----------------------------------------

-----------------------------------------
Table: Professions
Fields: id, title
Data: 1, Herbalism
2, Alchemy
-----------------------------------------

I'm trying to write a query for mySQL that will return: Cheveyo, Herbalism, Alchemy

I have tried:
select Members.name, Professions.title
FROM Members, Professions
WHERE Members.prof1=Professions.id;

That works to return: Cheveyo, Herbalism
But when I try:
select Members.name, Professions.title, Professions. title
FROM Members, Professions
WHERE Members.prof1=Professions.id and Members.prof2=Professions.id;

It doesn't return any results.

Any advice on what I'm doing wrong? or how to fix it?

Thanks,
Ranthalion

[srinika]

Select [Name], (Select Title from Professions where id = M.Prof1) , (Select Title from Professions where id = M.Prof2)
from members M

[Ranthalion]

Thanks for the response, but that does not seem to work for me. Whenever I run the query, a syntax error is returned. I'm using phpMyAdmin to run the query agains mySQL 5.0. I think it has a problem with the parenthesis.

Is this an uncommon type of query, or do I have my tables structured in a strange way or something? I'm still very new to SQL. My actual query has more to it, but this is the only part that is throwing me off...

Any other suggestions?

[olivthill]

Is the following syntax accepted?
Select [Name],
Title1 IN (Select Title from Professions where id = M.Prof1),
Title2 IN (Select Title from Professions where id = M.Prof2)
from members M
or
Select M.Name,
P1.Title,
P2.Title
from members M, Professions P1, Professions P2
where
P1.id = M.Prof1
and P2.id = M.Prof2

[Ranthalion]

Excellent! The second option there works perfectly. I didn't realize I had to reference the Professions table twice. You've been a great help.

Thanks,
Ranthalion

[klintan]

...do I have my tables structured in a strange way or something?

If a member always has two professions the structure is alright. However, if a member can have any number of professions then a structure like this is better:

-----------------------------------------
Table: Members
Fields: MemberId, name,
Data: 1, Cheveyo
-----------------------------------------

-----------------------------------------
Table: Professions
Fields: ProffesionId, title
Data: 1, Herbalism
2, Alchemy
-----------------------------------------

-----------------------------------------
Table: MemberProfessions
Fields: MemberId, ProfessionId
Data: 1, 1
1, 2
-----------------------------------------

And your SQL will look like this:


SELECT name, title
FROM Members
INNER JOIN MemberProfessions ON Members.MemberId=MemberProfessions.MemberId
INNER JOIN Professions ON MemberProfessions.ProfessionId=Professions.ProfessionId


However it will not look exactly like you specifed your results, it will return

Cheveyo, Herbalism
Cheveyo, Alchemy