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

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

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?

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?

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.