How can I write an SQL statement to get distinct data from 3 different tables at once?

I have 3 tables. Each contains data about shipments. Several fields are the same, but each has some that are different. I need to be able to pull the distinct SO# (sales order numbers) and WO# (work order numbers) from all 3 tables based on a customer name. I've tried joining the tables and selecting out this data, but I apparently don't know how to do it correctly, because I keep getting errors. Please help.How can I write an SQL statement to get distinct data from 3 different tables at once?
SELECT DISTINCT CustomerName, ServiceOrderNo, WorkOrderNo


FROM CustomerTable


INNER JOIN WorkOrderTable ON CustomerName.CustomerNo = WorkOrderTable.CustomerNo


INNER JOIN ServiceOrderTable ON CustomerName.CustomerNo = ServiceOrderTable.CustomerNo





If you need futher detail from the tables then use the above as a nested query within the larger query (email me with full details if you need further assistance)How can I write an SQL statement to get distinct data from 3 different tables at once?
You could make use of the SQL UNION operator.





Select distinct SO, WO, col3 from table1 where customer_name = ';John Doe';


UNION


Select distinct SO, WO, col3 from table2 where customer_name = ';John Doe';


UNION


Select distinct SO, WO, col3 from table3 where customer_name = ';John Doe';





Thats a very simple workaround for your situation BUT the caveat is that you are required to have exactly the same number of columns in each select query AND similar data types.





Hope that helps you, if not provide more details.
You would do an join statement such as





sql=';SELECT visitors.*, register.myday, register.programme, register.mydate FROM visitors RIGHT JOIN register ON visitors.memberid=register.memberid ORDER BY register.mydate DESC, register.programme ';





this query would select all field from a table called visitors, the myday, programme and mydate fields from the registers table where the member id on both tables is the same. The last bit ORDER By... tells it to display them by in numerical/alphabetical descending order by the mydate field first and then secondly the programme field.








http://www.w3schools.com/Sql/sql_join.as鈥?/a>





Hope this helps





Regards





Web Design Midlands


http://www.webdesign-midlands.co.uk
No offense, but if you have to query 3 tables to get an answer, your database structure is wrong.


Before designing a DB, lay down the questions you will ask. Then, the structure becomes obvious.
Several of the other responders have sorta correct answers, but to what degree depends on just how the tables relate to each other and what you're trying to get out of them:





If the recordset you want contains a mix of data from the tables, but each row has data in the same number and type of columns, a UNION might be appropriate.





If each resultset row has a bit of information for each table in every case, probably need an INNER JOIN.





If most of the resultset row has data from a table that is always there, but you want to show additional data from the other table(s) if it exists, then you want to do a LEFT (or RIGHT) JOIN.

Bracelet

necklace