distinct(whatever)

[piersk]

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:

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 )

[buntine]

Ok, this shouldnt be too hard to achieve with some nifty SQL work.

SQL statement:

SELECT iwc.suppliers, sensors.suppliers, valves.suppliers FROM iwc, sensors, valves 

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.

[piersk]

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.

[buntine]

Ok, we could try using the UNION statement to only select distinct records.

SQL query:

SELECT Suppliers FROM iwc
UNION
SELECT Suppliers FROM sensors
UNION
SELECT Suppliers FROM valves 

That might work a bit better.

Regards,
Andrew Buntine

[CardboardHammer]

SELECT DISTINCT Suppliers FROM
  (SELECT Suppliers FROM iwc
  UNION
  SELECT Suppliers FROM sensors
  UNION
  SELECT Suppliers FROM valves)

[buntine]

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

[piersk]

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.

[buntine]

no..

SELECT Suppliers FROM iwc 
UNION 
SELECT ColumnTwo FROM sensors 
UNION 
SELECT ThirdColumn FROM valves 

Regards.

[piersk]

Yes, you're right. It is possible. Thanks for your help.

[piersk]

*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:

SELECT DISTINCT(supplier) FROM (SELECT supplier FROM sensors UNION SELECT systemsupplier FROM iwc UNION SELECT valvesupplier FROM valves)

Everything else is the same.

[buntine]

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.

[piersk]

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.