Ad:

MS SQL Discussion Thread
Unsolved
Views: 1036

Aug 24th, 2008

SQL Query Help needed!

Expand Post »

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

Similar Threads

SQL SERVER 2000 Selecting a record based on an aggregate function in MS SQL
mysql and php display commands help needed in MySQL
Speed of member profiles in DaniWeb Community Feedback
cannot understand why i am getting the wrong results. in Oracle
WizardSteps-> SQL Insert -> Grab ID field in ASP.NET

newMeg

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

21 posts
since Jul 2006

Permalink

Aug 24th, 2008

Re: SQL Query Help needed!

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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Aug 24th, 2008

Re: SQL Query Help needed!

Click to Expand / Collapse Quote 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:

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

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?

newMeg

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

21 posts
since Jul 2006

Permalink

Aug 25th, 2008

Re: SQL Query Help needed!

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?

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Aug 26th, 2008

Re: SQL Query Help needed!

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

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

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: SQL Command Misbehaving under VStudio 2005

Next Thread in MS SQL Forum Timeline: How to retrieve table names

DaniWeb Message

Cancel Changes

User Name
Password