Query JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query JOIN

Author

Topic

misterraj
Yak Posting Veteran

94 Posts

Posted - 2008-06-10 : 02:05:44

i have two tables field1 is the primary key:
table1
--------------------
field1|field2|field3
---------------------

table2
---------------------
field1|field4|field5
---------------------
what we need is to get the results based on field1 matching datas, but is also a requirement that if there is no matching data in field1 of table2 w.r to table1 we just need to return table1 datas with null values for table2 fields.

is that possible, i used inner, right joins but all in vain.

what I need is a output like this

output
---------------------------------------
field1|field2|field3|field4|field5
---------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 02:08:22

Use a left join like this

SELECT t1.field1,t1.field2,t1.field3,t2.field4,t2.field5
FROM table1 t1
LEFT JOIN table2 t2
ON t2.field1=t1.field1

misterraj
Yak Posting Veteran

94 Posts

Posted - 2008-06-10 : 02:13:50

but this gives multiple records but what i require is only one record.
as per your suggestion i wrote

SELECT t1.field1,t1.field2,t1.field3,t2.field4,t2.field5
FROM table1 t1
LEFT JOIN table2 t2
ON t2.field1=t1.field1
and t1.field1 ='microsoft'

instead of getting the field1 record with microsoft, i have got all records from the table1 with null values of field3 and field4 from table2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 02:17:51

quote:
Originally posted by misterraj

but this gives multiple records but what i require is only one record.
as per your suggestion i wrote

SELECT t1.field1,t1.field2,t1.field3,t2.field4,t2.field5
FROM table1 t1
LEFT JOIN table2 t2
ON t2.field1=t1.field1
WHERE t1.field1 ='microsoft'

instead of getting the field1 record with microsoft, i have got all records from the table1 with null values of field3 and field4 from table2.

Use the filter condition in WHERE

misterraj
Yak Posting Veteran

94 Posts

Posted - 2008-06-10 : 02:20:59

well i got it! i had to add the where clause

the final solution looks like

SELECT t1.field1,t1.field2,t1.field3,t2.field4,t2.field5
FROM table1 t1
LEFT JOIN table2 t2
ON t2.field1=t1.field1
where t1.field1 ='microsoft'

hmm.. thanks for the response

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 02:30:31

quote:
Originally posted by misterraj

well i got it! i had to add the where clause

the final solution looks like

SELECT t1.field1,t1.field2,t1.field3,t2.field4,t2.field5
FROM table1 t1
LEFT JOIN table2 t2
ON t2.field1=t1.field1
where t1.field1 ='microsoft'

hmm.. thanks for the response

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources