1

In SQL Server 2012, I have two tables, one called carthead and the other called cartrows. Each table is probably 10k rows. I have identity columns in both tables, idorder is an identity column in carthead and idrow is an identity column in cartrows (though I don't seem to need to use this, I think, but I'm including it here so you have the background).

I'm trying to find the newest (or last-entered) idorder number (again, an identity column) where a product # = 97 and imgexists is True (it's a flag that's set if we have an image of it).

This query runs:

SELECT TOP 1 cr.idorder 
FROM cartrows cr, carthead ch 
WHERE cr.idproduct = 97 
  AND ch.imgexists IS NOT NULL
ORDER BY cr.idorder DESC

However, the answer spits out a number for the latest row where idproduct = 97 but not taking into consideration ch.imgexists is not null (I've tried other variations, including <> '', is not False, etc with the same result).

I began my search finding this thread: ( How to read the last row with SQL Server ) and just thinking I could add conditions to suit my needs, but apparently I'm doing it wrong..

Any thoughts? Kind thanks to all from Austin, Texas..

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jasonCbraatz
  • 11
  • 4
  • 1
    you are forgetting a join condition... and i'd use explicit joins vs your comma seperated list in the from clause – S3S Apr 17 '18 at 17:21
  • gotcha; where would I add the join [and what kind? INNER JOIN?].. for example, – jasonCbraatz Apr 17 '18 at 17:23
  • you need to introduce a join condition between the two used tables, cartrows and carthead with using a where clause or a join clause. – SOAL ABDELDJALLIL Apr 17 '18 at 17:23
  • I would recommend you stop using the `,` joins between the tables. In the long run it's going to be better if you always use `join` or `left outer join` etc. – James Z Apr 17 '18 at 17:24
  • so i have this: SELECT top 1 cr.idorder FROM rossirovetti..cartrows cr, rossirovetti..carthead ch inner join carthead.idorder ch on cartrows.idorder cr where cr.idproduct = 97 and ch.imgexists is not null ORDER BY cr.idorder DESC ... but it breaks: An expression of non-boolean type specified in a context where a condition is expected, near 'cr' – jasonCbraatz Apr 17 '18 at 17:28

2 Answers2

1

You need a join condition to limit the rows returned... something like

SELECT top 1 
   cr.idorder 
FROM 
   cartrows cr
INNER JOIN 
   carthead ch on
   ch.idproduct = cr.idproduct  --or what ever the tables are related by...
WHERE 
   cr.idproduct = 97 
   and ch.imgexists is not null 
ORDER BY 
   cr.idorder DESC

Also, I'd take a look at this post regarding your joins

Community
  • 1
  • 1
S3S
  • 24,809
  • 5
  • 26
  • 45
0

@scsimon had the right answer here, I need to do more studying on JOINS in general; thank y'all for the help..

SELECT top 1 
   cr.idorder 
FROM 
   cartrows cr
INNER JOIN 
   carthead ch on
   ch.idorder = cr.idorder
WHERE 
   cr.idproduct = 97 
   and ch.imgexists is not null 
ORDER BY 
   cr.idorder DESC

worked.. ;-)

jasonCbraatz
  • 11
  • 4