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

then use this only

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

else consider write outer join for non matching records.

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

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

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

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) ;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.