getting data from two tables

everity

Hi,
I'm new to this and I'm sure it must be something simple/silly that I'm missing!

I have 2 tables which have the following structure:

table 1
id | client name

table 2
id | title | client_id | .....more cols

I want to reference both tables, list the distinct client_ids from table 2 showing the client_name which appears in table 1.

That part has worked fine so far using:

SELECT DISTINCT
table1.id,
table1.client_name
FROM table1,
table2
WHERE table1.id=table2.client_id
ORDER BY client_name

My trouble is that now, I need to be able to retrieve table2.id as well and then reference it in my php when I create a list on my html page.

Can anyone tell me how I do this? I have tried lots of combinations of things but just seem to be getting it wrong every time.

thank you in advance

emeraldcat

This might be a stupid question, but why not put client name in table 2 and get rid of table 1 altogether?

Sxooter

So, I'm assuming that you can have >1 title per client? If so, then don't put the client name in the titles table as was recommended. It would generally be a bad idea for data integrity. The first rule of normalization is to never enter the same data twice.

Given that, your query seems capable. I'd switch to join on () syntax:

select c.id, c.client_name, t.title, t.moreinfo
from client c
join title t
on (c.id=t.client_id)

The join on () syntax is more easily readable, and then you can put a real where clause, with things like where client_name like ... and such in that. Also, many query planners can make better decisions when the join clause is presented in this way. Lastly, you then use a left or right join to get all the rows that don't match on one side or another, or a full join for all rows, on both sides, that match or don't.

everity

Thank you,
That was really clear and helpful - I'll give it a try that way.

You're right by the way, there are mulitple titles per client, so it makes sense to store client info in a separate table.