Select within a Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select within a Select

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-19 : 20:14:33

Hi again.

I have two tables.

Table A
locid, id, lcreator, status, issue
11, 133, John, Open, whatever
14, 144, Jane, Open, jdjdjds
15, 533, Bob, Closed, djdjs

Table B
id, place
10, Toronto
11, Winnipeg
12, Waterloo
14, Herethere

I am currently running a query on Table A as such:
strSQL = "select * from TableA order by status,locid"
and outputting the values into an HTML table
set objRS = objConn.Execute(strSQL)
lid = objRS("locid")

strSQL2 = "select place from TableB where id =" & lid
set objR4 = objConn.Execute(strSQL2)
name = objr4("place")

When reading all the values from TableA, I have to match tablea.locid to tableb.id and return the value of tableb.place

For example Table A:
11, 133, John, Open, whatever
will perform "select place from TableB where id =11" and give me "Winnipeg" as being location #11's proper name.

14, 144, Jane, Open, jdjdjds
will perform "select place from TableB where id =14" and return "Herethere" as the proper name.

I need a more efficient way to take the LOCID from tableA and select it from TableB's ID to ghet the name.

Any help would appreciated. I know this is putting strain on my SQL Server.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-19 : 22:38:27

what you need is call a JOIN. You join a table with another via a column(s). In your case it is like


SELECT a.locid, a.id, a.lcreator, a.status, a.issue, b.place
FROM   TableA a
INNER JOIN TableB b
ON     a.locid = b.id

KH
[spoiler]Time is always against us[/spoiler]

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-20 : 09:20:39

It worked perfectly. I'm going to try to remember this for future use. Thank you!

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-20 : 20:56:02

I'm having an issue with another table and trying to read a column from a secondary table.

Currently I do this:
SQL = "SELECT username,flddatetime FROM onlineu ORDER BY FLDDATETIME DESC"

-- onlineu table --
username / flddatetime
John, 1/12/2012 1:00:00 am
Jim, 1/12/2012 1:13:00 am

This gives me online users in the order of most recent.

I then want to read each recordset and perform a second query of a table named 'myusers' to see if the user has decided to be 'hidden' and therefore not show his username and last logged in time.

Do Until oRS.EOF
name = objrs("username") 'select onlineu.username
' perform second query using onlineu.username by looking up myusers.hideme

SQL2 = "SELECT hideme FROM myusers where username = '" & name & "'"
Set oRS2 = oConn.Execute(SQL2)
hide = ors2("hideme")

So basically I am querying two tables but would like to merge them. Based on the above post by Khtan, I tried this solution:

SQL = "SELECT a.username, a.flddatetime, b.hideme FROM onlineu a INNER JOIN myusers b ON a.username = b.username"

I receive "Invalid column name 'username'"

Note that both tables have a field named username but I think SQL knows the difference. If need be I could rename the onlineu column.

Help? :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-20 : 21:14:18

[code]SELECT a.username, a.flddatetime, b.hideme FROM onlineu a INNER JOIN myusers b ON a.username = b.username[/code]
Try to run the above query in a query window and verify that the column name in the table onlineu and myusers is correct, no typo err etc..

KH
[spoiler]Time is always against us[/spoiler]

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-20 : 22:53:29

Ahh it worked, I had sterilized the table names and forgot to change one back. First time I've ever used the query window in SQL too. So much to learn.

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-21 : 08:56:22

Sorry to bump this yet again but I just found another issue.

Part of my script displays Guests that are logged on (non-registered members that come to the site as guests). Now since they don't have a username I assign them a username equal to their Session ID (a random number given to each new connection).

Example:
SELECT a.username, a.flddatetime, b.hideme FROM onlineu a INNER JOIN myusers b ON a.username = b.username

-- onlineu table --
username / flddatetime
John, 1/12/2012 1:00:00 am
Jim, 1/12/2012 1:13:00 am
Guest21303892, 1/12/2012 2:22 am
Guest3940283, 1/14/2012 1:33 am

-- myusers --
"username" column
John
Jim

but there are no Guest accounts in the myusers table so I'm not getting any results for them as a.username = b.username when in fact a.username might also NOT exist in the b.username table.

Is there a fix for this?

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2012-09-21 : 09:45:48

With INNER JOIN you're telling SQL server to bring back only records where the username exists in BOTH of your tables.

You can replace your INNER JOIN with LEFT OUTER JOIN


SELECT a.username, a.flddatetime, b.hideme 
FROM onlineu a 
LEFT JOIN 
myusers b 
ON a.username = b.username

This is telling SQL Server to bring back ALL records from the LEFT table whether it finds a match for the username or not.

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-21 : 21:09:30

Worked perfect. Thank you. Now I know what JOIN does, which is probably on the first page of SQL 101 for Newbies.

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-21 : 23:10:23

You're going to love me again for bumping this.

Going back to my original query to read Mytable and get the proper location name from 'sites' based on the assigned 'locid' from Mytable

SELECT a.locid, a.ldate, a.id, a.lcreator, a.status, a.issue, b.town FROM Mytable a INNER JOIN sites b ON a.locid = b.id

I'm trying to now do two things.
1) Order by location ID and Status
2) Exclude status where it is equal to "Exclude"

It is a trouble ticket system I am designing and it returns open and closed status tickets but I want ones with status='exclude' not to be returned.

Attempts:
SELECT a.locid, a.ldate, a.id, a.lcreator, a.status where a.status <> 'Exclude', a.issue, b.town FROM Mytable a INNER JOIN sites b ON a.locid = b.id order by a.locid,a.status asc

SELECT a.locid, a.ldate, a.id, a.lcreator, a.status, a.issue, b.town FROM Mytable a INNER JOIN sites b ON a.locid = b.id order by locid,status asc

SELECT a.locid, a.ldate, a.id, a.lcreator, a.status, a.issue, b.town FROM Mytable a INNER JOIN sites b ON a.locid = b.id where a.status <> 'Exclude' order by a.locid,a.status asc"

All result in errors... I'm close but not quite close. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-22 : 00:26:44

your 3rd attempts query should work. What error message do you get from the 3rd query ?

KH
[spoiler]Time is always against us[/spoiler]

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-22 : 02:01:46

SELECT a.locid, a.ldate, a.id, a.lcreator, a.status, a.issue, b.town FROM mytable a INNER JOIN sites b ON a.locid = b.id where a.status <> 'Exclude' order by a.locid,a.status asc

Doh! My mistake, I had the status as 'Excluded' not 'Exclude'

However

The sort is out of order. The image below is ascending and as you can see, it also throws in out of order numbers (6189). The status is "Open" for all records so locid (first column shown below) should be displayed from lowest to highest regardless.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-22 : 02:17:32

in your 2nd image, the 1st column is order numbers or locid ?

KH
[spoiler]Time is always against us[/spoiler]

oap
Yak Posting Veteran

60 Posts

Posted - 2012-09-22 : 02:38:02

Oh man... it was "id" (autogenerated column). I changed it to order by a.id and it works.

Sorry, that's two in a row of my own error. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-22 : 03:00:57

quote:
Originally posted by oap

Oh man... it was "id" (autogenerated column). I changed it to order by a.id and it works.

Sorry, that's two in a row of my own error. :)

Cool

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources