CODEWITHSUNDEEP
mysqlgroup-bydistinct
Brett
Brett

Reputation: 20049

Getting unique group by values over 4 different columns

Ok, what I'm trying to do is try and get unique values from one table that span 4 separate columns.

For example, I have this below query which works correctly on one of the columns..

SELECT 
    rest.cuisine1, c.name
FROM
    specials
    INNER JOIN restaurant AS rest ON specials.restaurantid=rest.id
    INNER JOIN cuisine AS c ON rest.cuisine1=c.id
WHERE
    dateend >= CURDATE()
AND
    (specials.state='VIC' OR specials.state = 'ALL') 
AND 
    specials.status = 1 
AND 
    rest.status = 1
GROUP BY
    c.id;

Now, rest.cuisine1 is one of the columns that contain the data. As expected this query returns unique values from that column only. The below being an example of what is returned:

12 Cafe
18 Asian
29 Coffee

There are 3 more columns in that table, those being:

rest.cuisine2
rest.cuisine3
rest.cuisine4

I could run the above query 4 times (one on each column) and THEN run the values through PHP to get only unique values from the 4 different result sets, however I was wanting to find out if I can get what I want all in the one query.

Upvotes: 0

Views: 89

Answers (2)

Brett
Brett

Reputation: 20049

This answer is based off of MahmoudGamal's answer he posted in a comment, which he deleted for some reason.

I used the below..

SELECT 
    c.id, c.name
FROM
    specials
    INNER JOIN restaurant AS rest ON specials.restaurantid=rest.id
    INNER JOIN cuisine AS c ON c.id IN (rest.cuisine1, rest.cuisine2, rest.cuisine3, rest.cuisine4)
WHERE
    dateend >= CURDATE()
AND
    (specials.state='VIC' OR specials.state = 'ALL') 
AND 
    specials.status = 1 
AND 
    rest.status = 1
GROUP BY
    c.id;

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

try this 

 SELECT 
    rest.cuisine1, c.name ,rest.cuisine2 , rest.cuisine3, rest.cuisine4
FROM
    specials
    INNER JOIN restaurant AS rest ON specials.restaurantid=rest.id
    INNER JOIN cuisine AS c ON rest.cuisine1=c.id
            INNER JOIN cuisine AS c2 ON rest.cuisine2=c2.id
            INNER JOIN cuisine AS c3 ON rest.cuisine3=c3.id
            INNER JOIN cuisine AS c4 ON rest.cuisine4=c4.id
WHERE
    dateend >= CURDATE()
AND
    (specials.state='VIC' OR specials.state = 'ALL') 
AND 
    specials.status = 1 
AND 
    rest.status = 1
GROUP BY
    c.id;

Upvotes: 1

Related Questions