1

In the pubs database I want to find out stores that have orders with more than one title on the order. I have joined the table but the logic to apply to find the order which have more than one title I can't understand

SELECT 
    title, stores.stor_name, S.ord_num, S.qty
FROM 
    sales S
JOIN
    titles T ON (S.title_id = T.title_id)
JOIN
    stores ON (S.stor_id = stores.stor_id)

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jennifer
  • 9
  • 4
  • 1
    Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Sep 26 '16 at 20:59

1 Answers1

0

something like 

    SELECT 
    title, stores.stor_name, S.ord_num, S.qty
FROM 
    sales S
JOIN
    titles T ON (S.title_id = T.title_id)
JOIN
    stores ON (S.stor_id = stores.stor_id)
where S.ord_num in (

    SELECT 
    S1.ord_num
FROM 
    sales S1
JOIN
    titles T1 ON (S1.title_id = T1.title_id)
JOIN
    stores  st ON (S1.stor_id = st.stor_id)
    group by S1.ord_num
    having count( distinct title)>1
    )
Kostya
  • 1,567
  • 1
  • 9
  • 15