SELECT DISTINCT Nested query

Databases
1

Hello all.

I’ve not quite got the syntax of my nested query right, Can anyone help please.


SELECT DISTINCT wneedham.case_study.case_study_id  FROM
 (SELECT * FROM wneedham.case_study, wneedham.manufacturer, wneedham.case_study_manufacturer, wneedham.sector, wneedham.case_study_sector
WHERE case_study.case_study_id = case_study_manufacturer.case_study_id
AND manufacturer.manufacturer_id = case_study_manufacturer.manufacturer_id
AND case_study.case_study_id = case_study_sector.case_study_id
AND sector.sector_id = case_study_sector.sector_id)
2
  1. Next time please tell us the error you’re getting :slight_smile:
  2. Why are you using a nested query? It seems to make no sense
  3. Outside of the nested query, wneedham.case_study doesn’t exist.
  4. Maybe you’ll have to give the result of the nested query an alias (AS aliasname)
  5. If the field case_study_id exists in more than 1 table, then I don’t know how your DB handles SELECT * (I mean the field names in the query result), so selecting that field might give you other problems. You can avoid that specifying the needed fields in the nested query SELECT.

SELECT DISTINCT
    case_study_id
FROM
  (SELECT
     wneedham.case_study.case_study_id
   FROM
       wneedham.case_study
     , wneedham.manufacturer
     , wneedham.case_study_manufacturer
     , wneedham.sector
     , wneedham.case_study_sector
   WHERE case_study.case_study_id = case_study_manufacturer.case_study_id
   AND manufacturer.manufacturer_id = case_study_manufacturer.manufacturer_id
   AND case_study.case_study_id = case_study_sector.case_study_id
   AND sector.sector_id = case_study_sector.sector_id
  ) AS a
3

Guido,

At the moment I am getting the same case study more than once in my results array.

So I need to select distinct case studies within the sub query.

The sub query will contain filters such as

“AND manufacturer_id = '‘32’”

Thanks

4

The point is, if you didn’t post a simplified version of the real query, and you don’t join the result of your nested query with some other table or nested query, then you could do without. Like this:


SELECT DISTINCT
    wneedham.case_study.case_study_id
FROM
    wneedham.case_study
  , wneedham.manufacturer
  , wneedham.case_study_manufacturer
  , wneedham.sector
  , wneedham.case_study_sector
WHERE case_study.case_study_id = case_study_manufacturer.case_study_id
AND manufacturer.manufacturer_id = case_study_manufacturer.manufacturer_id
AND case_study.case_study_id = case_study_sector.case_study_id
AND sector.sector_id = case_study_sector.sector_id
AND manufacturer.manufacturer_id = '32'
5

Also - are you planning on adding additional fields/filters for each of these joined tables? If you’re not selecting a field from them or filtering off them, then I wouldn’t include them in your select statement. Or if you’re only filtering by ids, you could simplify it even further…

So based on your previous statement with the filter, all you’d need for the statement would be


SELECT DISTINCT
needham.case_study.case_study_id
FROM
wneedham.case_study
, wneedham.case_study_manufacturer
WHERE case_study.case_study_id = case_study_manufacturer.case_study_id
AND case_study_manufacturer.manufacturer_id = '32'

Or better still (slightly more efficient since the parser won’t have to convert yours to this format)


SELECT DISTINCT s.case_study_id
  FROM wneedham.case_study s
   JOIN wneedham.case_study_manufacturer sm ON sm.case_study_id = s.case_study_id
WHERE sm.manufacturer_id = '32'

Just because you know the business logic behind a table doesn’t mean you need to select from it. If you’re not searching by, say a date or the manufacturer and you’re just searching by a lookup field, then only use the lookup table in you query. Depending on the size of the database and the quality (or lack there of) of your indexes, your searches could be a magnitude of time quicker.

6