Distinct and sub-queries?

Hi all.

I'm hoping you can point me in the general direction of what I need to do.

I've got 2 tables (abbreviated):

orders: order_id, id, sessionid
products: id, title, ref

What I need is a select query which will pull all the info from both but display only one row per orders.id.

I think I need to use DISTINCT but I've read that it doesn't work with JOINs - is there a way of doing a subquery to pull the rest of the information out?

I know this is wrong but something like:

PHP Code:

---

SELECT DISTINCT orders.id (SELECT orders.sessionid, products.id, products.title from orders, products) WHERE sessionid='$sessionidnow' 


---

Does that make any sense?

Any suggestions welcomed!

of course DISTINCT works with JOINs

the part i'm having trouble with is understanding how the two tables are related

perhaps you could give the real table layouts?

also, if you want only one row per order, which one should it be?

You see! Never believe what you read!!

Ok so, this is for an informal wish-list rather than a proper cart (no need for quantities or prices etc). The user hits an "add to list" button on various products and that adds a row in the order table which adds an order_id, the product id and the current session id of the user.

So what should happen is when the user goes to the wish list page they get a list of the products they've added - but since they could in theory add a product multiple times I want the product to be listed only once.

The tables are (With an example):

Products:

id | ref | title
----------------
20 | abc1 | Chair
21 | abc2 | Table
22 | abc3 | bookcase

Orders:

order_id | id | sessionid
-----------------------
2 | 20 | 1234556676778888990etc
3 | 20 | 1234556676778888990etc
4 | 21 | 1234556676778888990etc

The wish-list page would list each distinct product ordered for that session id.

So I need a select query that will end up like this for the user's sessionid:

Ref | Title
--------------
abc1 | Chair
abc2 | Table

Hopefully that makes a little more sense!

yes it makes sense

let me suggest something else instead of screening for duplicates on the way out of the database -- screen for duplicates on the way in

Code:

---

ALTER Orders ADD UNIQUE ( id , sessionid )

---

that will prevent dupes automagically

then to retrieve a user's wish list, just run this --

Code:

---

SELECT p.ref
    , p.title
  FROM Orders AS o
INNER
  JOIN Products AS p
    ON p.id = o.id
 WHERE o.sessionid = 123093701230937012309370etc

---

Hi Rudy - thanks for this.

I haven't come across ALTER before (yes I know - I really must put some time aside to learn this stuff properly!) so I had a google to try to figure it out.

I thought that it would be best to re-create the table with the two unique fields - (didn't know you could have two uniques!). BUT now if the user tries to enter a product more than once they get the duplicate field error message.

How would I stop that?

Also - probably not an issue but since one of these uniques is a session id is there a possibility of exceeding max-length of the fields?

Edit: Rudy - I've found a way around this which generally makes life easier - before submitting the data to the table, checking if a row exists with the id and sessionid in there - if not then it runs the insert query. I don't know if this is a good approach or not but it seems to work! Thanks again for your help.

checking first is not quite the best strategy, because that's twice as many SQL statements as you need

if a user enters a product more than once, you ~want~ the database to return an error

when your app detects the error, you can turn it into a user-friendly message that says something like "sorry, you've already entered that product"

this would be the same message that you're displaying now after doing the SELECT, except you don't need to do the SELECT before the INSERT, just do the INSERT

Ah ok - thanks Rudy, I'll have another go.