SitePoint Sponsor

User Tag List

Results 1 to 20 of 20

Thread: SQL Query help

  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Query help

    Hi Guys I need a bit of help with some SQL.

    I have an Access database table called features, it contains a list of *all the possible* features a property might have, views, furnishing, parking spaces, garden etc. It has the following structure

    Features
    featureid PK
    name

    I have another table, PropertyFeatures, this table contains a list of the actual features a property has. It has the following structure:

    PropertyFeatures
    id PK
    featureid FK
    propertyid FK

    Now on my property editor interface I have a list of all possible features with checkboxes and I tick the checkbox if the property has this feature.

    I need to write a query that will give me an output of the featureid, name and a boolean value as to whether the property in question has this feature.

    So far I have:

    Code:
    SELECT F.featureid, F.[name], IIF(PF.Featureid is null, 0,1) as [bol] FROM 
    features as F LEFT JOIN propertyfeatures as PF on PF.featureid =F.featureid
    But obviously this doesn't narrow it down to a treatment of an individual property's feature. So I added WHERE propertyid=2

    Code:
    SELECT F.featureid, F.[name], IIF(PF.Featureid is null, 0,1) as [bol] FROM 
    features as F LEFT JOIN propertyfeatures as PF on PF.featureid =F.featureid WHERE propertyid=2
    But this then only returns the property features that have actually been selected by this property and does not return the unselected features with the boolean value 0. Bit stuck on where to go from here.

    Hopefully I've explained myself clearly enough.

    Can anyone help?

    Thanks in advance.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Putting a where condition on a left joined table makes the join an INNER JOIN. Instead of adding the condition to the WHERE, add it to the ON
    Code:
    SELECT 
        F.featureid
      , F.[name]
      , IIF(PF.Featureid is null, 0,1) as [bol] 
    FROM features as F 
    LEFT JOIN propertyfeatures as PF 
    ON PF.featureid = F.featureid 
    AND PF.propertyid=2

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guido you are right of course, but I don't think that type of join condition is supported by Access. I tried your suggestion and got a "JOIN expression not supported" error.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I think it is, but Access kind of s*cks (IMO) with its syntax. Try adding some parenthesis:
    Code:
    ON ( (PF.featureid = F.featureid)
    AND (PF.propertyid=2) )

  5. #5
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I commend you on your genius, thank you!
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  6. #6
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Further help needed

    Hello again, sorry to bump this thread but I need a little further help which is related to my question last week.

    I now need to show a page that lists all potential property types along side a checkbox and to check the the box when the customers requirements match that property type. For example a customer may be looking for a 'studio' or an 'apartment' in which case these two options should be checked but 'house' and 'bungalow' unchecked. You get the idea.

    The property types table is simple and uncomplicated.

    PropertyTypes
    typeid PK
    propertytype


    The customer's property requirements is stored in a bit of an odd way and my question will really centre on whether I can work with this data structure and just need some clever SQL or whether I have to change the data structure.

    PropertyRequirements
    id PK
    type
    locations
    beds
    minprice
    maxprice
    keywords

    Here the type field contains a comma separated list of propertytype ids For Example: '1, 2' for 'Studio' or 'Apartment'.

    So I tried writing an SQL statement with an IN clause on my Join:

    Code:
    SELECT PT.typeid, PT.propertytype, IIF(PR.type is NULL, 0,1) as [bol] FROM propertytypes as PT LEFT JOIN propertyrequirements as PR on ((PT.typeid IN (Cstr(PR.[Type]))) AND (PR.clientid=2))
    I found this worked only if there was a single value in the PR.Type field EG: '1' but not if there were multiple values '1, 2'

    I'm not sure if I can even reliably use the IN clause in a JOIN like this - is there any way of working with this data structure and writing SQL That will get a reliable result or do I need to rework this data structure?
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you cannot use IN like this, because as far as the sql is concerned, PR.[Type] is a single value

    so '1' will only be found if PR.[Type] is '1', but not if it's '1,2'

    IN is essentially a shortcut for a series of equality tests combined with ORs

    try this instead --

    ... ON InStr(PR.[Type],PT.typeid) > 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks

    One small problem though, there are 11 records in the propertytypes table. If the Property requirements are '11, 2' then this solution matches typeid 1 as a hit, as '1' occurs within '11'.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Here the type field contains a comma separated list of propertytype ids For Example: '1, 2' for 'Studio' or 'Apartment'.
    The solution is to normalize your database

  10. #10
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought so... its a pig to do that though with all the data already in it.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SortedSites View Post
    If the Property requirements are '11, 2' then this solution matches typeid 1 as a hit, as '1' occurs within '11'.
    you can avoid this by concatenating commas around both the search string and the searched string...

    ... ON InStr( ','&PR.[Type]&',' , ','&PT.typeid&',' ) > 0

    and, of course, it'll always require a table scan...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its a fudge, but its a good one that will save a load of work, thanks r937.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  13. #13
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Still more help needed

    I think I've been staring at this for two long because my mind is mush

    Trying to run a keyword search on properties with certain characteristics. Table structure (abridged)

    Properties
    propertyid PK
    locationid FK
    typeid FK
    beds
    price

    Propertytypes
    typeid PK
    propertytype

    Locations
    locationid PK
    location

    PropertyFeatures
    id PK
    featureid FK
    propertyid FK

    Features aka (characteristics)
    featureid PK
    name

    I need to search for properties of particular typeids in particular locationids priced between A and B and with characteristics like "disabled access" and "sea view"

    Code:
    SELECT P.*, PT.propertytype, L.location FROM 
    (((properties as P INNER JOIN locations as L on L.locationid=P.locationid) 
    INNER JOIN propertytypes as PT ON P.typeid = PT.typeid) 
    INNER JOIN propertyfeatures as PF on PF.propertyid= P.propertyid) 
    INNER JOIN features as F on PF.featureid=F.featureid
    
    WHERE P.typeid IN (1, 2, 3) 
    AND P.locationid IN (22, 9, 50, 6, 42, 35, 47, 46, 34, 1) 
    AND P.price BETWEEN 100000 AND 300000
    This is getting me nowhere as its already returning multiple instances of the same property matches and its before I've added the clause for checking the characteristics against F.name.

    Thoughts?
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    to see where the "multiple instances" are coming from, study your FROM clause

    you have a many-to-many relationship between properties and features, and your FROM clause retrieves all features for each property which satisfies the WHERE conditions

    so you're getting one row per feature per property

    and yet, your SELECT clause does not include any feature columns, so the results that you see are multiple rows of the same property

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

  15. #15
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Makes perfect sense, I just don't know to correct my joins to traverse the many-to-many relationship.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SortedSites View Post
    I just don't know to correct my joins to traverse the many-to-many relationship.
    your joins don't need correcting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but even if I add F.[name] to the fields in SELECT clause I get multiple match instances, how can I reduce this to single record matches so I can produce a human friendly list of results?
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  18. #18
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hold on Rudy, do you think I might get this if I read the chapter of your site point book 'Implementing a Many-to-many
    Relationship:'

    You seem to be covering a very similar scenario.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SortedSites View Post
    but even if I add F.[name] to the fields in SELECT clause I get multiple match instances
    no, you're getting single matches, but multiple rows for each one, corresponding to the multiple features that each one has

    if you exclude any F columns from the SELECT clause, you can use DISTINCT to return only the properties
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Forgot to come back and thank you for your help DISTINCT solved my problem, cheers.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com


Tags for this Thread

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
  •