CODEWITHSUNDEEP
sql
sil
sil

Reputation: 2110

SQL: Fetching one row across a link table join

Imagine I have two tables, food and people, and I indicate who likes which food with a link table. So:

foods
-----
sausages
pie
Mars bar

people
------
john
paul
george
ringo

person | food (link table)
-------+-----
john   | pie
john   | sausage
paul   | sausage

I'd like to get a list of foods, along with a person who likes that food. So I'd like a table like this:

food     | a randomly chosen liker
---------+------------------------
sausage  | john (note: this could be "paul" instead)
pie      | john (note: must be john; he's the only liker)
Mars bar | null (note: nobody likes it)

Is it possible to do this in one query?

Obviously, I can do:

select
    f.food, p.person 
from
    food f inner join link l
        on f.food = l.food
      inner join person p
        on l.person = p.person

but that will give me two sausage rows, because two people like it, and I'll have to deduplicate the rows myself.

Upvotes: 1

Views: 86

Answers (4)

Abdul Rehman Sayed
Abdul Rehman Sayed

Reputation: 6672

Another variant.. (Assuming it is SQL Server )

Select 
   a.Food, b.Person 
from
   foods a
outer apply
(
    Select top 1 Person from linkTable b where a.Food = b.Food
) b

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Do LEFT JOINs to also get food that no-one likes. GROUP BY to get each food only once, use MIN to pick first person that likes that food.

select f.food, min(p.person)
from food f
    left join linktable l on f.id = l.food_id
    left join people p on p.id = l.person_id
group by f.food

Upvotes: 1

sameh.q
sameh.q

Reputation: 1709

I would use a partition to do that, example:

WITH ORDERED AS
(
    SELECT 
        PERSON,
        FOOD,
        ROW_NUMBER() OVER (PARTITION BY lower(FOOD) ORDER BY lower(PERSON) DESC) AS RN
    FROM
    (

        SELECT 'john'  AS PERSON ,'pie'     AS FOOD FROM DUAL UNION
        SELECT 'john1' AS PERSON ,'sausage' AS FOOD FROM DUAL UNION
        SELECT 'john2' AS PERSON ,'sausage' AS FOOD FROM DUAL UNION
        SELECT 'john3' AS PERSON ,'sausage' AS FOOD FROM DUAL UNION
        SELECT 'john4' AS PERSON ,'sausage' AS FOOD FROM DUAL UNION
        SELECT 'john5' AS PERSON ,'eggs'    AS FOOD FROM DUAL UNION
        SELECT 'john6' AS PERSON ,'sausage' AS FOOD FROM DUAL UNION
        SELECT 'dada'  AS PERSON ,'sausage' AS FOOD FROM DUAL UNION
        SELECT 'paul'  AS PERSON ,'sausage' AS FOOD FROM DUAL

        -- Your select statement here that links the two tables

    ) PERSON_FOOD

)
SELECT
    FOOD,
    PERSON
FROM
    ORDERED
WHERE
    RN = 1

This will get you the following:

FOOD     |  PERSON
-------------------
eggs     |  john
pie      |  john
sausage  |  paul

This is in oracle syntax

Upvotes: 0

Uttam Kumar Roy
Uttam Kumar Roy

Reputation: 2058

select f.food, min(l.person)
from food f
    left join link l on f.foods = l.food
group by f.food

Upvotes: 0

Related Questions