Help with joining 2 queries - 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)

Help with joining 2 queries

Author

Topic

megala
Starting Member

23 Posts

Posted - 2008-08-24 : 23:13:34

Hi,
I need help on this sql. I have a table a and b and i need to select where a.id=b.id and b.mid=1 and few hardcoded a.id which does't exist in table b. i used left outer join but its still listing all in a table. Inner join doesnt return anything as well.

This is the inner join sql:



SELECT * FROM TBL_ComponentList c inner JOIN TBL_Modules m ON m.Identifier=c.CompIdentifierID and c.CreatedBy = '2418' and m.identifier IN ('1','2','3','4','5','6')

If i do it as 2 separate sql, i get what i need but i need to make it as 1. The 2 sql that i need to join is


1) SELECT * FROM TBL_Modules WHERE identifier IN ('1','2','3','4','5','6') ORDER BY Identifier
2) SELECT * FROM TBL_Modules m,TBL_ComponentList c WHERE  m.Identifier=c.CompIdentifierID  AND c.CreatedBy = '2418' ORDER BY m.Identifier

The identifier IN id 1 to 6 doesn't exist in table componentlist.

Any help pls?

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-24 : 23:16:58

please post some sample data and the required result

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 00:01:42

quote:
Originally posted by megala

Hi,
I need help on this sql. I have a table a and b and i need to select where a.id=b.id and b.mid=1 and few hardcoded a.id which does't exist in table b. i used left outer join but its still listing all in a table. Inner join doesnt return anything as well.

This is the inner join sql:

SELECT * FROM TBL_ComponentList c inner JOIN TBL_Modules m ON m.Identifier=c.CompIdentifierID and c.CreatedBy = '2418' and m.identifier IN ('1','2','3','4','5','6')

If i do it as 2 separate sql, i get what i need but i need to make it as 1. The 2 sql that i need to join is

1) SELECT * FROM TBL_Modules WHERE identifier IN ('1','2','3','4','5','6') ORDER BY Identifier
2) SELECT * FROM TBL_Modules m,TBL_ComponentList c WHERE m.Identifier=c.CompIdentifierID AND c.CreatedBy = '2418' ORDER BY m.Identifier

The identifier IN id 1 to 6 doesn't exist in table componentlist.

Any help pls?

Thank you

If it doesnt exist in second table what are you trying to achieve by joining to second one?

megala
Starting Member

23 Posts

Posted - 2008-08-25 : 00:11:02

SELECT identifier as id,title,createdby as M_CreatedBy FROM TBL_Modules WHERE identifier IN ('1','2','3','4','5','6') ORDER BY Identifier
SELECT m.identifier as id ,c.CompIdentifierID as CompID,m.title,m.createdby,c.createdby as c_createdby FROM TBL_Modules m,TBL_ComponentList c WHERE m.Identifier=c.CompIdentifierID AND c.CreatedBy = '2418'

The result is need after combining both query is:

Id CompID Title M_CreatedBy c_createdby

7 7 L 0 2418
8 8 S 0 2418
9 9 R 0 2418
10 10 C 0 2418
18 18 W2 2418 2418
19 19 W1 2418 2418
20 20 W3 2418 2418
21 21 W4 2418 2418
1 Null K 0 Null
2 Null B 0 Null
3 Null P 0 Null
4 Null A 0 Null
5 Null V 0 Null
6 Null M 0 Null

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 00:15:14

try this

SELECT  m.identifier 		AS id ,
        c.CompIdentifierID 	AS CompID,
        m.title,
        m.createdby,
        c.createdby 		AS c_createdby 
FROM 	TBL_Modules m LEFT JOIN TBL_ComponentList c 
ON	m.Identifier	= c.CompIdentifierID 
WHERE 	AND c.CreatedBy = '2418'

KH
[spoiler]Time is always against us[/spoiler]

megala
Starting Member

23 Posts

Posted - 2008-08-25 : 00:36:31

HI KH, thanks for the reply but the result of the sql u suggested does not include the hardcoded id i mentioned (identifier IN ('1','2','3','4','5','6')).

and the result of ur sql is

[url]http://www.ineers.com/sql/KH_result.JPG[/url]

if i change the sql to
SELECT m.identifier AS id ,
c.CompIdentifierID AS CompID,
m.title,
m.createdby,
c.createdby AS c_createdby
FROM TBL_Modules m LEFT JOIN TBL_ComponentList c
ON m.Identifier = c.CompIdentifierID
where c.CreatedBy = '2418' and m.identifier IN ('1','2','3','4','5','6')

nothing is returned. empty records.

Hi visakh16,

the reason i need to get the value that doesnt exist is because i need to display all component and the components in table component list is whatever added by the user, whereelse identifer I to 6 is what provided by the user. That is why i need to disply all.

Can this be achieved? thank you.

megala
Starting Member

23 Posts

Posted - 2008-08-25 : 00:38:39

the actual result that i need is something like this.

[url]http://www.ineers.com/sql/result.JPG[/url]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 00:42:02

Can you please please post your table DDL and sample data for the tables ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 00:43:47

[code]SELECT m.identifier AS id ,
c.CompIdentifierID AS CompID,
m.title,
m.createdby,
c.createdby AS c_createdby
FROM TBL_Modules m
LEFT JOIN TBL_ComponentList c
ON m.Identifier = c.CompIdentifierID
AND c.CreatedBy = '2418'
WHERE c.CompIdentifierID IS NOT NULL
OR m.Identifier IN ('1','2','3','4','5','6')[/code]

megala
Starting Member

23 Posts

Posted - 2008-08-25 : 02:07:35

Hi visakh16,

didnt see that this will do the trick.


c.CompIdentifierID IS NOT NULL
OR m.Identifier IN ('1','2','3','4','5','6')

it works now. Thanks alot.

khtan thanks to u too for helping out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 02:20:39

quote:
Originally posted by megala

Hi visakh16,

didnt see that this will do the trick.

c.CompIdentifierID IS NOT NULL
OR m.Identifier IN ('1','2','3','4','5','6')

it works now. Thanks alot.

khtan thanks to u too for helping out.

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources