SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Many to many search query

    Let's say I have a table Cars with a id, brand, model etc., and table Equipment with id, name, description.
    The two tables are connected by a junction table (many to many relationship).
    I want to run a query: "find all cars with brand named Ford and equipment named ABS and ESP", so I'll find all Fords equiped with at least ABS and ESP.

    Cars (id, brand_id, model...)
    CarsToEquipment (car_id, equipment_id)
    Equipment (id, name, description)
    Brands (id, name...)
    ...

    What is most efficient query for this?

    At the moment I use something like this (I shortened it), but I suppose it could be done in a better way (or improved):

    Code:
    	SELECT
    		 c.id
    		,c.model
    		,c.year
    		,c.price
    		,c.desc
    		,c.power
    		,b.name AS brand
    		,f.name AS fuel
    		,t.name AS type
    	FROM
    		Cars c
    		LEFT JOIN
    		Brands b
    			ON c.brend_id = b.id
    		LEFT JOIN
    		Fuels f
    			ON c.fuel_id = f.id
    		LEFT JOIN
    		Types t
    			ON c.type_id = t.id
    	WHERE
    		b.name = 'ford'
    		AND
    		2 = (	SELECT
    				COUNT(*)
    			FROM
    				CarsToEquipment cte
    				LEFT JOIN
    				Equipment e
    					ON cte.equipment_id = e.id
    			WHERE
    				cte.car_id = c.id
    				AND
    				e.name IN ('abs', 'esp')
    		)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    all your joins should be INNER

    otherwise that looks fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you.

    But, about joins... Definitely I should use INNER JOIN for Brands and inside subquery (CarsToEquipment cte INNER JOIN Equipment e), but if I use all INNER JOIN, Car with unknown Fuel or Type etc. (not mandatory fields) won't be shown?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you are right, not mandatory means the foreign key can be null, so those would be okay as LEFT OUTER JOINs

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •