Help With Sql Code (Selecting From Multiple Tables)

[iNko]

Got these tables:

 

Table1

ID USER

1 User1

2 User2

 

Table2

ID CAR

1 Car1

2 Car2

 

Table3

ID STREET

1 Street1

2 Street2

 

If i wanned to display USER and CAR columns from Table1 and Table2, i would write:

SELECT USER, CAR

FROM Table1, Table2

WHERE Table1.ID = Table2.ID

 

What if i wanned to add a third table (Table3), how does the code change?

SELECT USER, CAR, STREET

FROM Table1, Table2, Table3

WHERE ........

[trq]

SELECT user, car, street
FROM table1
 LEFT JOIN table2 USING(id)
 LEFT JOIN table3 USING(id)

[iNko]

thx for the quick reply! i tried using ur code but its not working like i wanted

 

Heres a better example of what i want to get:

 

Table1

id number

1 1234

2 4321

 

Table2

id username

1 random_username

 

Table3

id password

2 random_password

 

What if i want to display all the numbers from table1, where id from table1 matches id from table2 AND table3?

if it was only 2 tables, this would work:

SELECT number

FROM Table1, Table2

WHERE Table1.ID = Table2.ID

 

im getting always getthing this error - field list is ambiguous

[Barand]

If you only want those matching use inner joins instead of left joins

 

SELECT table1.number
FROM table1
 INNER JOIN table2 USING(id)
 INNER JOIN table3 USING(id)

[iNko]

cant edit..

SELECT Table1.sukurimo_data, Table2.issiuntimo_data, Table3.issiuntimo_data

FROM Table1, Table2, Table3

WHERE Table1.table_id= Table2.table_id

OR Table1.table_id= Table3.table_id

 

This is how its displayed to me:

 

 

how can i do this??

 

Edit: also when i try this:

 

SELECT table1.number

FROM table1

INNER JOIN table2 USING(id)

INNER JOIN table3 USING(id)

 

It returns an empty result, no errors

Edited December 3, 2012 by iNko

[Psycho]

iNko, the previous posters are trying to help you do "proper" JOINS. The process you have been using is a normal join that JOIN every record from both tables together then excludes the ones you don't want. There is a ton of functionality using the different JOINs and if you don't learn them, you will be stuck in how much you can do.