joining table

[srikanth2321]

Hi

I'm facing a problem. I want to join two table which are in the following format.

Table 1

id - - name
1 - - xx
2 - - yy
3 - - zxz

Table 2

id -- xx -- yy
1 -- 1 -- 1
2 -- 2 -- 1
3 -- 1 -- 2

I want make a query which can get join two tables based on the value given by the user in the column yy in the second table. For example, I will expect the wuery to return the following table if I search for '1' in column yy (table 2)

result table

c_id -- name -- xx -- yy
1 -- xx -- 1 -- 1
2 -- yy -- 2 -- 1
3 -- zxz

I want the entire rows in the table 1 with the values what I search for in the table 2. IF the search value is not there in table 2 then the result table for that paticular id should be empty.

I tried to write

select * from table 1 left join table 2 using (id) where yy =1

It is working good but not able to return the rows from table1 which doesn't have the value yy =1.

I hope it a bit clear.

Any help would be great.

thanks

[debasisdas]

then use this only

select * from table 1 left join table 2 using (id)

else consider write outer join for non matching records.

[srikanth2321]

thanks, but I think we should use where to sort the rows in the second table

[debasisdas]

WHERE clause is not used for sorting in a SQL statement.

[srikanth2321]

ok sorry, What I mean is to filter

Actually I want a query which can give me the result as follows from the two tables mentioned above.

result table

c_id -- name -- xx -- yy
1 -- xx -- 1 -- 1
2 -- yy -- 2 -- 1
3 -- zxz

[srikanth2321]

This is a working solution at the moment. But if I use the name of the columns instead of '*', it is not giving the proper results

select * from Table 1 left outer join (select c_id,xx,yy from Table 2 where yy= 1) X using (c_id) ;