Reputation: 41

I'm stuck with my SELECT sql query

I'm having a problem with my SELECT sql statement and I haven't figured it out yet. When I print out the results using mysql_fetch_assoc() function, I get repetitive rows/records. A record is repeated 13 times. I don't know why and I have done it right as far as my knowledge tells me.

The following is my sql query:

SELECT 
    members.member_id, 
    members.firstname, 
    members.lastname, 
    billing_details.Street_Address, 
    billing_details.Mobile_No, 
    orders_details.*, 
    food_details.*, 
    categories.*, 
    cart_details.*, 
    quantities.* 
FROM 
    members, billing_details, orders_details, categories, 
    quantities, food_details, cart_details 
WHERE 
    members.member_id=orders_details.member_id AND 
    billing_details.billing_id=orders_details.billing_id AND 
    orders_details.cart_id=cart_details.cart_id AND 
    cart_details.food_id=food_details.food_id AND 
    cart_details.quantity_id=quantities.quantity_id

Upvotes: 0

Answers (4)

Janus Troelsen

Reputation: 21270

If you only want each member_id to appear once, you can use DISTINCT:

SELECT DISTINCT members.member_id, ...

You can also use JOIN USING to avoid repetition (DRY):

http://dev.mysql.com/doc/refman/5.7/en/join.html (search for USING(column_list))

That way, you'd also have noticed the msising predicate, since it would be right next to the joined table name.

Upvotes: 0

Yogus

Reputation: 2272

Why dont you make left Joins like below

SELECT members.member_id, members.firstname, members.lastname, billing_details.Street_Address, billing_details.Mobile_No, orders_details.*, food_details.*, categories.*, cart_details.*, quantities.* FROM members as m

Left join billing_details as b ON b.billing_id=m.?
LEFT JOIN orders_details as o ON o.cart_id=m.?
LEFT JOIN food_details as f ON f.f_id =m.? 
LEFT JOIN cart_details as c ON c.?=?
LEFT JOIN quantitiesas q  ON q.?=?
LEFT JOIN categories as cat ON cat.?=?

Upvotes: 1