Select all columns from one table and one from another where column equals variable

Question

Sorry for the long title.

I have a statement which needs to grab all the columns from one row from BinConfig:

SELECT * 
FROM BinConfig WITH(NOLOCK) 
WHERE IssuerKey = @IssuerKey

But I also need to grab a single column from one row from CardRangeGroup also based on that IssuerKey column.

What I've tried:

SELECT 
    BinConfig.*, CardRangeGroup.Name 
FROM 
    BinConfig 
JOIN 
    CardRangeGroup WITH(NOLOCK) 
WHERE 
    @IssuerKey = BinConfig.IssuerKey 
    AND @IssuerKey = CardRangeGroup.IssuerKey

Which gives me a syntax error near WHERE. I've tried to find resources online, but everywhere I look I can't find anything explaining how to select rows based on a passed in variable. Any help?

You're missing the `ON` portion of the `JOIN` statement, SQL needs to know how the tables connect! — alybaba726, Jul 02 '15 at 16:01
if this is MySQL then WITH(NOLOCK) is not supported. See this for equivalent http://itecsoftware.com/with-nolock-table-hint-equivalent-for-mysql — PaulF, Jul 02 '15 at 16:03

jpw · Accepted Answer · 2015-07-02T16:08:18.643

5

You need to specify how the tables should be joined. Try this:

SELECT BinConfig.*, CardRangeGroup.Name 
FROM BinConfig 
JOIN CardRangeGroup ON BinConfig.IssuerKey = CardRangeGroup.IssuerKey
WHERE @IssuerKey = CardRangeGroup.IssuerKey

The with(nolock) might not be needed (or a good idea) so I removed it.

edited Jul 02 '15 at 16:08

answered Jul 02 '15 at 16:03

jpw

44,361
6
66
86

1

I don't know how I forgot that, I'm fairly new to SQL and just learning about Join statements. Thank you! – Toby Caulk Jul 02 '15 at 16:04
Also, I tagged the question wrong. This is actually SQL-Server, is the `with(nolock)` function still supported? – Toby Caulk Jul 02 '15 at 16:05
@TobyCaulk Yes, but it might be bad to use though, see http://stackoverflow.com/questions/686724/sql-server-when-should-you-use-with-nolock and http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ – jpw Jul 02 '15 at 16:07
I've heard how bad it can be, but I was told to use it so I still have to. Thank you for warning me though! – Toby Caulk Jul 02 '15 at 16:09

score 1 · Answer 2 · answered Jul 02 '15 at 16:06

1

try this , you don't need to use where

 SELECT BinConfig.*, CardRangeGroup.Name FROM BinConfig JOIN   
 CardRangeGroup
 ON CardRangeGroup.IssuerKey = BinConfig.IssuerKey AND @IssuerKey = CardRangeGroup.IssuerKey

answered Jul 02 '15 at 16:06

Karthik

4,950
6
35
65

Sure, the _where_ clause is optional, but it makes the intent clear though (in my opinion). – jpw Jul 02 '15 at 16:09
sorry, I dint mean to offend. I was just saying. – Karthik Jul 02 '15 at 16:10

Select all columns from one table and one from another where column equals variable

2 Answers2