Query returns 3 of every record? please help

[diddydustin]

Hey guys,

I'm using the following query:

SELECT r.intQueID
,ri.chrISBN
,case isnull(p.intProductID,0)
WHEN 0 THEN
case ri.chrRecordStatus WHEN 'E' THEN -1 ELSE 0 END
ELSE 1
END bAvalible
FROM dbo.tblQuery r
INNER JOIN dbo.tblQueryItem ri
ON r.intQueID = ri.intQueID
LEFT OUTER JOIN tblProduct p
ON p.chrISBN = ri.chrISBN

WHERE sdtExpires > getdate()


It returns results like these:
47 0020005105 0
47 0020005105 0
47 0020005105 0
47 0020016700 0
47 0020016700 0
47 0020016700 0
47 0020080956 0
47 0020080956 0
47 0020080956 0
47 002009745X 0
47 002009745X 0
47 002009745X 0
47 0020100604 0
47 0020100604 0
47 0020100604 0
47 0020103514 0
47 0020103514 0
47 0020103514 0
47 0020103700 0
47 0020103700 0
47 0020103700 0
47 002011690X 0
47 002011690X 0
47 002011690X 0

However, the table looks like this:
47
0020005105 A 2005-06-13 13:33:48.357 47
0020016700 A 2005-06-13 13:33:48.357 47
0020080956 A 2005-06-13 13:33:48.357 47
002009745X A 2005-06-13 13:33:48.357 47
0020100604 A 2005-06-13 13:33:48.357 47
0020103514 A 2005-06-13 13:33:48.357 47
0020103700 A 2005-06-13 13:33:48.357 47
002011690X A 2005-06-13 13:33:48.357

Why is this query returning 3 of everything? Thank you!

Dustin

 

[jbenson001]

Your tblQueryItem table probably has multiple rows per ID

 

[diddydustin]

No, the tblQueryItem table does not have multiple rows for each ID... the query generates 3 rows for each 1. Do you see from the example above?

 

[jbenson001]

the data you show is what table.. maybe you have multiple ISBNs in your product table...

give data from each table

 

[SQLBill]

Run this:

SELECT COUNT(chrISBN)
FROM tblQueryItem
WHERE chrISBN = 0020005105

(If chrISBN is not Integer, but single quotes around the ISBN).

What result did you get?


-SQLBill

Posting advice: FAQ481-4875

 

[diddydustin]

It returns 1.

 

[vongrunt]

Same query, but on table tblProduct?
(replace tblQueryItem with tblProduct and run again).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLBill]

What happens if you change the LEFT OUTER JOIN to an INNER JOIN?

Also, you might first want to 'fix' this and test it:

 INNER JOIN dbo.tblQueryItem ri
    ON r.intQueID = ri.intQueID
 LEFT OUTER JOIN tblProduct p
   ON [b]p.chrISBN = ri.chrISBN[/b]

This is backwards. The ON is supposed to have the same order as the table order. The type of join is on the tables NOT on the ON command. So you are joining tblQueryItem (left) to tblProduct (right).

Try this:

 INNER JOIN dbo.tblQueryItem ri
    ON r.intQueID = ri.intQueID
 LEFT OUTER JOIN tblProduct p
   ON ri.chrISBN = p.chrISBN

-SQLBill


Posting advice: FAQ481-4875

 

[Catadmin]

I think SQLBill has nailed your problem, that it is in the way you have your join ON clauses written. If what he recommends still pulls up three records, try commenting out the last join clause and see how many records it pulls up.

Usually, the way I locate errors in my code is by isolating various bits and pieces (commenting out stuff I'm not testing) and running them to see if they give the desired result. If so, I change my isolated bits until I've gone through the entire piece of code. Then I start combining things, little by little until I hit the piece where it's acting funny.





Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???