www.webdeveloper.com
Results 1 to 3 of 3

Thread: Complex query advice. Multiple INNER JOINs and count results with a certain perameter

  1. #1
    Join Date
    Apr 2011
    Location
    Seattle, WA
    Posts
    265

    Question Complex query advice. Multiple INNER JOINs and count results with a certain perameter

    Okay, here's what I have so far:

    Code:
    SELECT
    	plat.title AS p_title,
    	plat.initial AS p_init,
    	builder.title AS p_builder,
    	address.city AS p_city
    INNER JOIN company AS builder ON plat.builder = builder.id
    INNER JOIN address ON plat.address = address.id
    INNER JOIN lot ON lot.plat = plat.id
    FROM plat WHERE display = 'local' AND current_item = 1
    I need the joined "lot" results to tell me how many lots with that "plat" id have a status of "AVL" (available now) and how many have a status of "CSN" (coming soon) and how many have a status of "SLD" or "CSD" (for sold/pending and closed)

    I also need the lot results to tell me the lowest and highest prices (lot.price) of available lots... I don't need the price of every lot, just the lowest and highest... and I need to add another join that links to "plan" for each lot (via lot.plan on plan.id) to tell me the lowest and highest plan.sqft for the lots.

    Also, if you think I screwed up what I've already got, advice for fixing that would be great too. I usually do several individual queries so I don't have much experience actually using JOINs, so I am trying to get some practice.

    Each row returned should be one "plat" (a community of new homes, a collection of lots, etc) and I have to display all of that data, which I used to take 5 or 6 queries to do.

    Any help is greatly appreciated. Thanks in advance!

  2. #2
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    795
    Only looking at the "lot" table, and creating a dummy version on my side (obviously column names will differ, but it's the concept I'm trying to show):

    Code:
    SELECT
      T.status,
      T.count_of,
      (SELECT MIN(l.price) FROM lot l WHERE l.status = T.status) AS min_price,
      (SELECT MAX(l.price) FROM lot l WHERE l.status = T.status) AS max_price,
      (SELECT MIN(l.sqft)  FROM lot l WHERE l.status = T.status) AS min_sqft,
      (SELECT MAX(l.sqft)  FROM lot l WHERE l.status = T.status) AS max_sqft
    FROM (
      SELECT
        l.status,
        COUNT(*) AS count_of
        FROM lot l
      GROUP BY
      l.status
    ) T
    Recordset

    status, count_of, min_price, max_price, min_sqft, max_sqft
    'AVL', 4, 1890, 8705, 875, 6191
    'CSD', 2, 250, 3123, 3924, 9479
    'CSN', 3, 937, 8383, 704, 9217
    'SLD', 6, 1044, 7676, 71, 7230
    Last edited by bionoid; 12-12-2013 at 12:26 PM.
    JavaScript: Learn | Validate | Compact | bionoid

  3. #3
    Join Date
    Sep 2011
    Posts
    59
    it is something like that

    SELECT
    plat.title AS p_title,
    plat.initial AS p_init,
    builder.title AS p_builder,
    address.city AS p_city,
    lt.min_price,
    lt.max_price,
    lt.min_sqft,
    lt.max_sqft,
    lt.count_of
    FROM plat
    INNER JOIN company AS builder ON plat.builder = builder.id
    INNER JOIN address ON plat.address = address.id
    INNER JOIN
    (
    SELECT distinct
    T.status,
    T.count_of,
    T.plat
    (SELECT MIN(l.price) FROM lot l WHERE l.status = T.status) AS min_price,
    (SELECT MAX(l.price) FROM lot l WHERE l.status = T.status) AS max_price,
    (SELECT MIN(l.sqft) FROM lot l WHERE l.status = T.status) AS min_sqft,
    (SELECT MAX(l.sqft) FROM lot l WHERE l.status = T.status) AS max_sqft
    FROM lot T
    inner join
    (
    SELECT
    status,
    plat,
    COUNT(*) AS count_of
    FROM lot
    GROUP BY
    status
    ) l
    on T.status = l.status
    and T.plat = l.plat
    ) lt
    ON lt.plat = plat.id
    WHERE display = 'local' AND current_item = 1

    If you will publish table structure with sample data it will be easy to twick

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles