Where/ And Clause in Join Statement to avoid duplicates

Question

Im new to SQL - I've looked through the posts but couldn't quite work out the answer to my question sorry. I'm trying to do what I think should be a simple query, using a where/ or statement in my join clause.

The problem is I seem to be getting duplicates. Here is the code I'm using:

  select A.[AuctionId], AA.attribute_id
  from [auction] A
  left join [auction_attribute] AA
  on AA.auction_id = A.AuctionId
  where AA.attribute_id = 127
  or (AA.attribute_id = 132 and AA.text_data = 'USABLE')

I want to get all records where the attribute ID is 127 and ALSO the attribute 132 for the records needs to have the associated text_data 'USABLE', if that makes sense? A record can have multiple attribute IDs so I want to get all 127s where the text_data associated with their 132 is also 'USABLE'. I had done the query with a sub-query originally but I think this would be a more efficient way to do it, if only I could get it right!

The above is currently returning duplicate records and if I substitute 'or' with 'and' I get no records. Can anyone help please??

Hello Cathy! Welcome to StackOverflow. Please mention the following: 1. What is your database server (is it MySQL or SQL Server or Oracle) 2. What is the actual output of your query? 3. What is your expected output? 4. Also, it will be more prudent if you post your table structure. — Choudhury Saadmaan Mahmid, Sep 22 '14 at 05:17
You need to first understand the basic database structures, including primary keys and foreign keys. Do Google those and try to understand them and then come back and edit your question. — Rachcha, Sep 22 '14 at 05:20
When you have the same attribute in more than one table, give it the same name: `auction_id` = `AuctionId`, so give them the same name. — Turophile, Sep 22 '14 at 07:00

Serge Ballesta · Answer 1 · 2014-09-22T07:56:28.383

EDIT : replace left outer joins with simple inner joins as suggested per Turophile (thanks)

You actual query means :

get all records that have an attribute of value 127, and also all records that have an attribute value of 132 associated to USABLE.

If it is really what you need, but without duplicate, the query could be like :

select distinct A.[AuctionId], AA.attribute_id
from [auction] A
     join [auction_attribute] AA
         on AA.auction_id = A.AuctionId
where    AA.attribute_id = 127
     or (AA.attribute_id = 132 and AA.text_data = 'USABLE')

Your description seems to be :

get records that have an attribute of 127 AND and an attribute of 132 associated to USABLE

If it is what you want, the query should be :

select A.[AuctionId], AA.attribute_id, AA2.attribute_id
from [auction] A
     join [auction_attribute] AA
         on AA.auction_id = A.AuctionId
     join [auction_attribute] AA2
         on AA2.auction_id = A.AuctionId
where    AA.attribute_id = 127
     and (AA2.attribute_id = 132 and AA2.text_data = 'USABLE')

Is SQL, you may use many times same table provided you give it different aliases.

Edit : As suggested by Turophile, I used simple inner joins in the queries. Outer join need only be used when you want to get records from one table even if other another table does not contain them.

Upvoted because I think the last part of the answer is what the questioner wanted. Here is a fiddle to illustrate it in action: http://sqlfiddle.com/#!3/1bf29/5 — Turophile, Sep 22 '14 at 07:01
Also, (for the benefit of the questioner) those joins could be standard inner joins, instead of left outer joins. — Turophile, Sep 22 '14 at 07:21

Where/ And Clause in Join Statement to avoid duplicates

1 Answers1