SELECT WHERE IN

Question

Post reply

SELECT WHERE IN

peter oconnell

Newbie

Points: 8
More actions
February 19, 2008 at 1:48 am

#182336

Hi,
I've got 2 tables and I want to return data where a value is also in another table. I'm using the following:
SELECT tagname FROM history
WHERE tagname IN (SELECT tagname FROM livedata)
AND value = 1
This created a never ending loop, if I replace the SQL in the brackets to valid data such as 'system', 'harddrive', 'cpu' then it works fine.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

snk1983 SSChasing Mays Points: 611 More actions · Answer 1

try this one .........

SELECT tagname FROM history as h

inner join livedata as l on l.tagname = h.tagname

where value = 1

peter oconnell Newbie Points: 8 More actions · Answer 2

Hi,

Thanks for this, unfortunately it appears I'm having issues with my laptop and as such its throwing up stupid errors like ambiguous column name even though I know it exists. I'll let you know how I get on 🙂

mark lake Say Hey Kid Points: 702 More actions · Answer 3

A slight alteration on that query:

SELECT h.tagname FROM history as h

inner join livedata as l on l.tagname = h.tagname

where value = 1

It's not that it can't find the fieldname it's that it can find both fields. You have to specify which tagname to use.

peter oconnell Newbie Points: 8 More actions · Answer 4

cheers, but I think its also an issue with my laptop, the history table cannot be found now 🙂 Think a format is in order!!

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Hi Peter,

I suggest making sure you are connected to the correct DB before going through with your format!

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

peter oconnell Newbie Points: 8 More actions · Answer 6

hehe yes I am, the laptop is slowly griding to a halt as software is added and removed constantly. I'm behind on the quarterly format anyway 🙂