Duplicate data

[Help!php]

I am trying to view all the product that was sold. If I try something simple like

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

It shows all the result. But now I want to add names to the results I am viewing this will mean joining another table. But when I join table it creates duplicate data. Not sure why. And if I try to distinct the orderid.. it still shows the duplicate orderid.

 

SELECT * FROM printersales p
LEFT JOIN orderdetail o ON ( p.orderid = o.orderid )
LEFT JOIN printers pr ON ( o.productid = pr.productid )
WHERE p.updated > '2012-02-2 00:00:01' ORDER BY o.orderid DESC

[Help!php]

I have three tables. Printersale, Orderdetails and Printers. Printersale is where most of the data that I need but I need to access Printers table to get the printername and the price. But I need to access orderdetails to get orderid and join this with Printersale and orderdetails productid to join with printers table. This way I can have all the information. But however orderdetails have duplicate orderid and I am not sure what to do.

 

Hopefully I didnt confuse any reader

[ManiacDan]

Can we see all the fields in these three tables?  That JOIN should work, unless there's multiple printers with the same product ID or multiple orders with the same order ID.

[Help!php]

Printersale table has

1.orderid

2.orderdate

3.updated

4. origsalesman

5.salesman

6.email

7.phone

8.ordertotal

9.status

10.profit

11.product

 

fields.

 

Orderdetail table has

 

1.orderdetailid

2.orderid

3.productid

4.price

5.quantity

6.fullfilled

7.shippeddate

8.description

 

has all these field.

 

and Printer table has

 

1.Productid

2.Name

3.rrp

 

all these field.

 

 

[Help!php]

Well it does work. but i am still getting two duplicate data. In orderdetails the orderid is duplicated and i dont know how to get rid of the duplicate orderid in orderdetails.

[ManiacDan]

So there's two orderdetails for one order?  Then you're getting two results, that's just how it works.  If this is a one-to-many relationship, you need to do two queries or have your code understand that some of this data will be duplicated.

[Help!php]

I didnt do the table. I am just working on someone else database. if it was one to many .. then how would the query be

[ManiacDan]

I'm not blaming you, I'm telling you how data works.  If you JOIN a table with one record to a table which has two records referencing table1's record, then the result will have two rows.  That's how SQL works.

 

You can either:

1)  Do separate queries so you fetch the order in one and all the details in another.

 

2)  Write your code so that it only prints the order once and then ignores it for the rest of the records which contain the details.

[Help!php]

I like your second printer but I have no idea how to write a query which only gets the first result :/..

 

Nah I wasnt saying you were blaming on me. i think the database has mistakes.

[Zane]

I have no idea how to write a query which only gets the first result :/..

use a LIMIT at the end of the query..

[ManiacDan]

You're not understanding what I'm saying.

 

You know what, back this up entirely.  What are you GETTING and why is it wrong?  What do you want to happen?  We know what data is in the table, show what you want the output to be like.

[Help!php]

No I do understand what you are saying.. I was meant to say I like your second idea..

 

So basically from these three tables I need orderid, orderdate, updated, name, rrp, origsalesman, salesman,status

 

FROM printersales

INNER JOIN orderdetail o ON ( p.orderid = o.orderid )

INNER JOIN printers pr ON ( o.productid = pr.productid )

WHERE p.updated > '2012-02-2 00:00:01' ORDER BY o.orderid DESC

 

After I make the Inner joint with orderdetails, orderid is showing twice because there are two entry in orderdetails.

 

Orderdetails is messed up table so I want to print only one orderid and other details that matches the orderid.

 

do you get it or not?? :-\

[Help!php]

at the moment it prints..

 

orderid      date        blah    blah    Printername  blah  blah

 

1211      12/02/2012  blah    blah    Epson211      blah  blah

 

1211      12/02/2011  blah    blag    Epson 210      blah  blah

 

So the orderid is getting repeated in orderdetails and prints two printername. is there a way where i can just get one result.. or is it beyond messed

[ManiacDan]

Well if you answered the question I'd be able to help you.  Go up and find the question.  So far you're saying you have two lines and you only want one of them.  "Just print one" is your answer unless you read and answer the last question.

[mikosiko]

at the moment it prints..

 

orderid      date        blah    blah    Printername  blah  blah

 

1211      12/02/2012  blah    blah    Epson211      blah  blah

 

1211      12/02/2011  blah    blag    Epson 210      blah  blah

 

So the orderid is getting repeated in orderdetails and prints two printername. is there a way where i can just get one result.. or is it beyond messed

 

Nothing there is duplicated or "messed" as Dan is trying to explain to you (option 2 in his answer)... that is how a JOIN works.... maybe a simple example help you to understand better...

 

 

Table_A

id  name

1  John

 

Table_B  (A_id is a FK to Table_A id)

id  A_id  classname

1      1  English

2      1  History

 

Query

SELECT a.id, a.name, b.classname

  FROM Table_A a

  INNER JOIN Table_B b ON (a.id = b.A_id);

 

 

 

An inner join essentially combines the records from two tables (A and B) based on a given join-predicate ON (a.id = b.A_id) in this case).

The SQL-engine computes the Cartesian product of all records in the tables. Thus, combines each record in table A with every record in table B.

Only those records in the joined table that satisfy the join predicate remain, and the final result set will be the composed of the fields that you choose from each table.

 

In the example above the final result set will look like this (records are not duplicated, only some fields)

1 John English

1 John History

 

then is up to you to decide how you want to display the results. Same concept apply with more tables.  In this forum are several examples that you can look at to see exactly the same situation and options showing how it is solved at display time  ... search for them.