0

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

3
Contributors
4
Replies
5
Views
9 Years
Discussion Span
Last Post by huangzhi
0

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:

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 id 1 to 6 doesnt exist in table componentlist.

Any help pls?

0

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?

0

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

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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.