www.webdeveloper.com
Results 1 to 12 of 12

Thread: distinct(whatever)

  1. #1
    Join Date
    Jun 2003
    Location
    Guildford, UK
    Posts
    153

    distinct(whatever)

    I have three tables (iwc, sensors and valves) and I want to get the number of different suppliers from these three tables. If I had just one table, I know I'd do this:
    Code:
    select distinct(column) from table"
    , but how would I go about doing this with three? Is it even possible?

    (btw, I'm using an access database)

    Thanks in advance (although I'll probably say thanks again at the end )

  2. #2
    Join Date
    Jan 2004
    Location
    Melbourne, Australia
    Posts
    5,298
    Ok, this shouldnt be too hard to achieve with some nifty SQL work.

    SQL statement:
    PHP Code:
    SELECT iwc.supplierssensors.suppliersvalves.suppliers FROM iwcsensorsvalves 
    We could also use the INNER JOIN statement to try and make a match, but that is not necessary here. Also, you will have to change the field name to suit.

    Regards,
    Andrew Buntine.

  3. #3
    Join Date
    Jun 2003
    Location
    Guildford, UK
    Posts
    153
    Sorry, I haven't made myself clear. I need to get a list of all suppliers that appear, but for each one only to appear once, no matter how many times they appear in each table.

    And changing the column name isn't really a valid option since this is quite a large site and I am only performing this particular search on one page.

  4. #4
    Join Date
    Jan 2004
    Location
    Melbourne, Australia
    Posts
    5,298
    Ok, we could try using the UNION statement to only select distinct records.

    SQL query:
    PHP Code:
    SELECT Suppliers FROM iwc
    UNION
    SELECT Suppliers FROM sensors
    UNION
    SELECT Suppliers FROM valves 
    That might work a bit better.

    Regards,
    Andrew Buntine

  5. #5
    Join Date
    Nov 2003
    Posts
    655
    Code:
    SELECT DISTINCT Suppliers FROM
      (SELECT Suppliers FROM iwc
      UNION
      SELECT Suppliers FROM sensors
      UNION
      SELECT Suppliers FROM valves)

  6. #6
    Join Date
    Jan 2004
    Location
    Melbourne, Australia
    Posts
    5,298
    The UNION statment should automatically select distinct records. Unless you decide to use the UNION ALL statement.

    Correct me if im wrong.

    Regards,
    Andrew Buntine

  7. #7
    Join Date
    Jun 2003
    Location
    Guildford, UK
    Posts
    153
    Oops, sorry, my bad. I have a further problem in that the three columns do not have the same name (I did not start this project, only came in about half way through and by that time it was too late). I'm now assuming that this will not be possible.

  8. #8
    Join Date
    Jan 2004
    Location
    Melbourne, Australia
    Posts
    5,298
    no..

    PHP Code:
    SELECT Suppliers FROM iwc 
    UNION 
    SELECT ColumnTwo FROM sensors 
    UNION 
    SELECT ThirdColumn FROM valves 
    Regards.

  9. #9
    Join Date
    Jun 2003
    Location
    Guildford, UK
    Posts
    153
    Yes, you're right. It is possible. Thanks for your help.

  10. #10
    Join Date
    Jun 2003
    Location
    Guildford, UK
    Posts
    153
    *cough* bump

    I've just switched this database over to SQL server, and this query with the UNIONs in it doesn't seem to work. The query that I'm using is:
    Code:
    SELECT DISTINCT(supplier) FROM (SELECT supplier FROM sensors UNION SELECT systemsupplier FROM iwc UNION SELECT valvesupplier FROM valves)
    Everything else is the same.

  11. #11
    Join Date
    Jan 2004
    Location
    Melbourne, Australia
    Posts
    5,298
    MS SQL Server has some limitations when it comes to the UNION [ALL] statement.

    They are not supported graphically, so you cannot use them in the Query Designer.

    After some searching, i found the following link, which may be helpful (Read the part about 'partially supported query types')

    http://msdn.microsoft.com/library/de...ssqlserver.asp

    Regards,
    Andrew Buntine.

  12. #12
    Join Date
    Jun 2003
    Location
    Guildford, UK
    Posts
    153
    Cheers for the link andrew.

    I'm not up to scratch on sql server (the only reason im using it is because some queries are v big and can't be run using access - it complains), but I'm using ADO to query the db, not any gui interface.

    Oh well... back to el drawing board.

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