Jump to content

Welcome to MSFN Forum
Register now to gain access to all of our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more. This message will be removed once you have signed in.
Login to Account Create an Account



Photo

Simple SQL query question

- - - - -

  • Please log in to reply
8 replies to this topic

#1
ciHnoN

ciHnoN

    Member

  • Banned
  • PipPip
  • 153 posts
  • Joined 22-January 13
  • OS:none specified
  • Country: Country Flag

Donator

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


He who laughs has not yet heard the bad news.


How to remove advertisement from MSFN

#2
Tripredacus

Tripredacus

    K-Mart-ian Legend

  • Super Moderator
  • 9,958 posts
  • Joined 28-April 06
  • OS:Server 2012
  • Country: Country Flag

Donator

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.
MSFN RULES | GimageX HTA for PE 3-5 | lol probloms
tpxmsfn1_zps393339c1.jpg

#3
ciHnoN

ciHnoN

    Member

  • Banned
  • PipPip
  • 153 posts
  • Joined 22-January 13
  • OS:none specified
  • Country: Country Flag

Donator

I'm using MSSQL. (Thanks)
He who laughs has not yet heard the bad news.

#4
submix8c

submix8c

    Inconceivable!

  • Patrons
  • 4,373 posts
  • Joined 14-September 05
  • OS:none specified
  • Country: Country Flag
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.

Someday the tyrants will be unthroned... Jason "Jay" Chasteen; RIP, bro!

Posted Image


#5
ciHnoN

ciHnoN

    Member

  • Banned
  • PipPip
  • 153 posts
  • Joined 22-January 13
  • OS:none specified
  • Country: Country Flag

Donator

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
He who laughs has not yet heard the bad news.

#6
submix8c

submix8c

    Inconceivable!

  • Patrons
  • 4,373 posts
  • Joined 14-September 05
  • OS:none specified
  • Country: Country Flag
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"

Someday the tyrants will be unthroned... Jason "Jay" Chasteen; RIP, bro!

Posted Image


#7
Tripredacus

Tripredacus

    K-Mart-ian Legend

  • Super Moderator
  • 9,958 posts
  • Joined 28-April 06
  • OS:Server 2012
  • Country: Country Flag

Donator

Sounds like you need to do a JOIN. Read about the types here (generally speaking)
http://www.w3schools...ql/sql_join.asp

Remember MSSQL and MySQL has slightly different syntax.
MSFN RULES | GimageX HTA for PE 3-5 | lol probloms
tpxmsfn1_zps393339c1.jpg

#8
ciHnoN

ciHnoN

    Member

  • Banned
  • PipPip
  • 153 posts
  • Joined 22-January 13
  • OS:none specified
  • Country: Country Flag

Donator

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:
He who laughs has not yet heard the bad news.

#9
ciHnoN

ciHnoN

    Member

  • Banned
  • PipPip
  • 153 posts
  • Joined 22-January 13
  • OS:none specified
  • Country: Country Flag

Donator

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



He who laughs has not yet heard the bad news.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users