Selective use of DISTINCT statement?

Cinquecento

Hi,

I've got this BIG select statement that joins three tables:

mysql> select nameCode,nameTitle,if((relateItem = 1) OR (relateLink = 1),'YES','NO') LINK from iName LEFT JOIN iRelate ON (nameCode = relateLink OR nameCode = relateItem) LEFT JOIN iRelType ON relateType = typeID WHERE nameCode <> 1;

The value '1' will be a variable that represents the product that we are currently browsing. The value of LINK is dependent on whether the product listed is related to product '1' or not. iRelate supplies the definition of the relationship.

Here are the results below:

+----------+---------------------+------+
| nameCode | nameTitle | LINK |
+----------+---------------------+------+
| 00000014 | Magruder X5000 | NO |
| 00000003 | StreetXX | YES |
| 00000010 | KRacer | NO |
| 00000009 | Aero Racer I | YES |
| 00000007 | Digger | NO |
| 00000013 | RuggedXX | NO |
| 00000011 | doodle | NO |
| 00000011 | doodle | YES |
| 00000012 | floop | NO |
| 00000015 | Flanders C200 | NO |
| 00000016 | Ministry of Defence | NO |
| 00000016 | Ministry of Defence | NO |
| 00000016 | Ministry of Defence | YES |
| 00000017 | Leviathan | NO |
| 00000018 | Scoot Titanium | YES |
| 00000019 | SpiderFive | NO |
| 00000020 | Super 1 Spoke | YES |
+----------+---------------------+------+
17 rows in set (0.00 sec)

What is wrong here is that I only want the product to appear once - with YES if it's linked of NO if it's not. Because the iRelate table shows that product 'doodle' is also linked to another product - it gets mentioned again. DISTINCT does not work - because LINK is included in it's remit.

Can DISTINCT be used just to compile the initial list of products an then LINK is evaluated outside of this?

Do you see what I mean?

Ian

What I'm after is a list of ALL products - and then

Cinquecento

I've tried using GROUP BY nameCode - it does the job - but because LINK is often 'NO' before it is 'YES' for the same product (such as 'doodle') it oftem gives the wrong outcome:

+----------+---------------------+------+
| nameCode | nameTitle | LINK |
+----------+---------------------+------+
| 00000003 | StreetXX | YES |
| 00000007 | Digger | NO |
| 00000009 | Aero Racer I | YES |
| 00000010 | KRacer | NO |
| 00000011 | doodle | NO |
| 00000012 | floop | NO |
| 00000013 | RuggedXX | NO |
| 00000014 | Magruder X5000 | NO |
| 00000015 | Flanders C200 | NO |
| 00000016 | Ministry of Defence | NO |
| 00000017 | Leviathan | NO |
| 00000018 | Scoot Titanium | YES |
| 00000019 | SpiderFive | NO |
| 00000020 | Super 1 Spoke | YES |
+----------+---------------------+------+
14 rows in set (0.00 sec)

Rodders

Would an OUTER JOIN do what you're after?

Lars_Berg

select nameCode,nameTitle,
case when sum(relateItem+relateLink) > 0 then 'YES' else 'No' end as LINK
 from iName LEFT JOIN iRelate ON (nameCode = relateLink OR nameCode = relateItem) LEFT JOIN iRelType ON relateType = typeID WHERE nameCode <> 1
group by nameCode,nameTitle

Cinquecento

I'm running 3.22 - none of these 'CASE' conditionals work - they must be a version 4 addition I guess!

Any other way around - I can only ever get to the same results...

Regards

Ian

Lars_Berg

select nameCode,nameTitle,
if(sum(relateItem+relateLink),'YES','No')  as LINK
 from iName LEFT JOIN iRelate ON (nameCode = relateLink OR nameCode = relateItem) LEFT JOIN iRelType ON 
relateType = typeID WHERE nameCode <> 1
group by nameCode,nameTitle

Cinquecento

Right - I get it now (for the time being) - the SUM is only TRUE if PRODUCT 1 appears in the iRelate table.....

I was close - but I recieved no cigar! You can have it - but share it with Rodders who helped out earlier last week!

Ian ;-)

Rodders

I don't smoke! Can I have a new guitar please?

Cinquecento

I've just noticed that I only have one entry in my prototype 'iRelate' table that DOESN'T connect Product 1.

relateItem | relateLink

  17         |        16
   16         |         1
   11         |          1
     1         |          3
      1        |           9

Product 17 is appearing in my results with YES - even though it's not connected to Product 1!

I'm trying hard - but I just can't see how to make the results of the test for a connection to Product 1 selective - for example:

16 IS connected to 1
3 IS connected to 1
17 IS NOT connected to 1

I'm sinking!

Ian