Something wrong with my query

[xxreenaxx1]

SELECT p.orderid, p.updated, p.origsalesman, p.salesman, p.status, p.profit, p.product, p.orderdate, o.price
FROM printersales p, printers pr, orderdetail o
WHERE o.productid = pr.productid
AND p.orderid = o.orderid
AND p.updated > '2011-08-5 00:00:01'

 

This is my code and when I do this query without

p.orderid = o.orderid

it gives me 29 results but if I do the query shown above it gives me 24 result so I am missing few field and not sure why. Any idea why its doing this? Do I have an error on my code??

[AyKay47]

possibly because that where clause is narrowing the results further. How many rows are you actually expecting, please give us some more information on the subject.

[kickstart]

Hi

 

Looks like if you miss out that line you will land up cross joining table p against the results of Joining o and pr.

 

Possibly more obvious with the query laid out as:-

 

SELECT p.orderid, p.updated, p.origsalesman, p.salesman, p.status, p.profit, p.product, p.orderdate, o.price
FROM printers pr
INNER JOIN orderdetail o ON o.productid = pr.productid
INNER JOIN printersales p ON p.orderid = o.orderid
WHERE p.updated > '2011-08-5 00:00:01' 

 

All the best

 

Keith

[The Little Guy]

Give this a try:

 

SELECT p.orderid, p.updated, p.origsalesman, p.salesman, p.status, p.profit, p.product, p.orderdate, o.price
FROM printersales p LEFT JOIN orderdetail o ON(p.orderid = o.orderid)
LEFT JOIN printers pr ON(o.productid = pr.productid)
WHERE p.updated > '2011-08-5 00:00:01';

[Help!php]

Thank you for the help! My account has been weird as I changed my email address and it said it sent an email activation which I havent got so I cant access the account.

 

So anyways. I tired these query and its giving me 28 results and its only suppose to give me 26 ..

 

When I try a plan query

 

SELECT * FROM printersales WHERE updated > '2011-08-5 00:00:01' ORDER BY orderid ASC

 

this query gives me 26 result and when I add few more fields such as printername and the price which are taken from different tables it goes all wrong

[PFMaBiSmAd]

The day part of your literal date value MUST have a leading zero, to give it two digits, for the greater-than comparison to work correctly.

[kickstart]

Hi

 

Good spot PFMaBiSmAd

 

this query gives me 26 result and when I add few more fields such as printername and the price which are taken from different tables it goes all wrong

 

Which suggests that one of the tables has multiple matching rows. Ie, if there are 2 orders for a single printer sale record that will bring back 2 rows.

 

All the best

 

Keith

[Help!php]

Thank you so much. It worked ...

 

Now I am having a problem with the PHP script that goes with this sql. I am going to post that on php section.

 

Thank you once again