Jump to content

Simple SQL query question


Recommended Posts

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! :)

Link to comment
Share on other sites


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.

Link to comment
Share on other sites

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"

Link to comment
Share on other sites

*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!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...