Sign in to follow this  
Followers 0
ciHnoN

Simple SQL query question

9 posts in this topic

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

0

Share this post


Link to post
Share on other sites

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.

0

Share this post


Link to post
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.

0

Share this post


Link to post
Share on other sites

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

0

Share this post


Link to post
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"

0

Share this post


Link to post
Share on other sites

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 :huh:

0

Share this post


Link to post
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!

0

Share this post


Link to post
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
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.