Simple SQL query question

[ciHnoN]

Sorry, I wasn't sure where to post an SQL specific question. Please move this thread to the correct place.

I think I'm getting stupid or something, not sure why I cannot solve this! *lol*

I got two tables TABLE1 and TABLE2. TABLE1 has some item ids which can have one or more linked values from TABLE2.

Example:

TABLE1.ID

TABLE1.ITEMID

TABLE1.table2_ID

TABLE2.ID

TABLE2.DESCRIPTION

Data sample:

TABLE1:

ID ---- ITEMID ---- table2_ID

1 ---- SOMEITEMa ---- 2

2 ---- SOMEITEMa ---- 3

3 ---- SOMEITEMa ---- 4

4 ---- SOMEITEMb ---- 2

5 ---- SOMEITEMc ---- 2

TABLE2:

ID ---- DESCRIPTION

1 ---- somedescription001

2 ---- somedescription002

3 ---- somedescription003

4 ---- somedescriptoin004

As you see, in TABLE1, I got 3 items "SOMEITEMa","SOMEITEMb","SOMEITEMc" which all share "description002" connected by TABLE2.ID to them.

My question is: How do I select from TABLE1 only those ITEMIDs which have TABLE2.ID='2' and NO OTHER IDs connected to them? I basically want the result to show two rows from TABLE1, which is "SOMEITEMb" and "SOMEITEMc", and not "SOMEITEMa" because that item has other values connected to it. Eventhough is has TABLE2.ID='2' connected to it, I don't want to select it because it has other IDs from TABLE2 as well.

Thanks!

[Tripredacus]

Which SQL are you using? MSSQL, MySQL or something else?

Should be close enough for web development. When in doubt, if there is no section we always have the Hardware and Software sections.

[ciHnoN]

I'm using MSSQL. (Thanks)

[submix8c]

Been a LOOOONG time using SQL (MainFrame via Assembly/COBOL) but it seems that somewhere you will have to allow for a "count" or another "secondary" Read to see if there's another "ITEMID" existing (thus indicating more than one Table2_ID and/or building the Report in an independent temporary Table?

How are the Key Fields set up - just the ID field in both tables? On a Mainframe, you could use a Secondary Key as necessary (depending on your needs).

(that's an oddball you're asking for)

Probably no help, but logically that seems to be what you may need to do.

[ciHnoN]

Thanks for the help submix8c but I really need an example of a SQL query. Kind'a urgent, need it for something at my work. hehe

[submix8c]

Sorry I can't help beyond "logically". Only have used basic functions in MS-Access so far (very similar). Now if this were being coded in said Mainframe languages (and access to said Mainframe), I could have give you exactly what you need post-haste. Have no full-blown SQL (MS-Access isn't AFAICT) installed.

"For Each..."/ "End-For"

[Tripredacus]

Sounds like you need to do a JOIN. Read about the types here (generally speaking)

http://www.w3schools.com/sql/sql_join.asp

Remember MSSQL and MySQL has slightly different syntax.

[ciHnoN]

Thanks for the link. I know about JOIN. I been coding a long while now. But this specific scenario doesn't cover it. A bit tricky. :e

[ciHnoN]

*solved it*

Turns out I had the IDs from second table so I only used one table.

Something like:

SELECT id FROM table

LEFT JOIN

(SELECT id FROM table WHERE <some condition>[mine was id<10 or id>20]) AS A

ON table.id=A.id

WHERE A.id IS NULL <---- the important part. <---- the important part.

Cheers!