query of 2 tables

pelegk2

i have this tables :

Prd
p_makat name
1 aaa
2 bbb
3 ddd

tbl
P_makat Set_no
1 4
2 3

this is the result

P_makat Set_no
1 4
2 3
3 NULL

2 questions :
1)how can i get the result from the first 2 tables?
2)how can i get for example the result of p_makat=3
from table 1 even if it dosent exist in table 2?
so i will see al table 1 detailes and from table 2 i will see that the valu dosent ecist?
thanks in advane
peleg

marian

if these two tables have an id which is common in both of them, you can use left join as follows:

select * from table1 as t1 left outer join table2 as t2 on t1.id=t2.id

goodluck

pelegk2

select * from common.products as cmm left outer join tblweightstations as t2 on cmm.p_makat=t2.p_makat and cmm.p_makat='6435'

but i get the message :

duplicate field name p_makat

what to do?
thnaks in advance
peleg

tsinka

Hi,

the problem is that p_makat exists twice so try something like

SELECT cmm.p_makat,t2.Set_no FROM ....

Thomas

pelegk2

but in this case if i wont havea ley in table2 i wont get any results!!!
in time that i do want at least a result from table 1

Roger_Ramjet

If you have a Left Join on tables with a common field then the Using form is the way to go

select cmm.p_makat, t2.set_no from common.products as cmm left join tblweightstations as t2 USING (p_makat) WHERE cmm.p_makat='6435'

The left join will always return cmm.p_makat, and Null in set_no if there is no entry in t2.