MS SQL

MS SQL RSS

SQL Query Help needed!

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Jul 2006

Posts: 21

Reputation: newMeg is an unknown quantity at this point

Solved Threads: 0

newMeg

Offline

Newbie Poster

SQL Query Help needed!

Aug 24th, 2008

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. Can anyone help pls.

Thank you

•

Join Date: Aug 2008

Posts: 1,160

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: SQL Query Help needed!

Aug 24th, 2008

you need to do an inner join if you are not wanting to list all in the tables that share the record ids.

•

Join Date: Jul 2006

Posts: 21

Reputation: newMeg is an unknown quantity at this point

Solved Threads: 0

newMeg

Offline

Newbie Poster

Re: SQL Query Help needed!

Aug 24th, 2008

•

Originally Posted by dickersonka

you need to do an inner join if you are not wanting to list all in the tables that share the record ids.

Hi, Thanks for the reply. I did an inner join and i'm still not getting the result. Nothing is queried out because of the c.CreatedBy = '2418' which doesnt exist for the identifier ('1','2','3','4','5','6').

This is the inner join sql:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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')
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
 
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
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 id 1 to 6 doesnt exist in table componentlist.

Any help pls?

•

Join Date: Aug 2008

Posts: 1,160

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: SQL Query Help needed!

Aug 25th, 2008

You said in the first you wanted a.id=b.id,
what are the columns for a.id and b.id?

But are you saying that a.id might not exist?

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

Re: SQL Query Help needed!

Aug 26th, 2008

You can combine your 2 query with 'UNION ALL' or 'UNION' like below:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT * FROM TBL_Modules WHERE identifier IN ('1','2','3','4','5','6') ORDER BY Identifier
union ALL
SELECT m.* FROM TBL_Modules m,TBL_ComponentList c WHERE  m.Identifier=c.CompIdentifierID  AND c.CreatedBy = '2418' ORDER BY m.Identifier
SELECT * FROM TBL_Modules WHERE identifier IN ('1','2','3','4','5','6') ORDER BY Identifier
union all
SELECT m.* FROM TBL_Modules m,TBL_ComponentList c WHERE  m.Identifier=c.CompIdentifierID  AND c.CreatedBy = '2418' ORDER BY m.Identifier