Multi Table Query 1

[lb1]

Hi,

I created a large database where I record information specific to each customer. Each table gives me a list of products, general information or others that I can retrieve easily for each customer. For example, my tables are as follows:

Table1:
Customer ID
Product Manufactured
etc..

Table2:
Customer ID
Product Copyrighted
etc..

Table3:
Customer ID
Company description
etc..
Etc…

I am trying to create a query that will allow me to create a list of all the Customer ID that would offer a specific occurrence of my search word. For example if I am looking for the word ‘widget’ in Table1. Product Manufactured , in Table2. Product Copyrighted , etc.. I would like to get a list of all the Customer ID that offer ‘Widget’.

Currently I search each table, one by one, and when I find a match, I add the Customer ID to another table. When I finish to read all the tables, I open the result table and remove all CustomerID double, sort by Customer ID and retrieve the name, address etc for each Customer ID. This works but it take a long time.

I suppose that there is a better and faster way to do this.

Does somebody could direct me to a better way?

Thanks in advance.
Louis

 

[JamesLean]

I take it Table3 is your main customer table? You could try this query:

SELECT DISTINCT customerid, companydescription
FROM table3 t3
  JOIN table1 t1 ON t3.customerid = t1.customerid
  JOIN table2 t2 ON t3.customerid = t2.customerid
WHERE t1.productmanufactured LIKE '%widget%'
  OR t2.productcopyrighted LIKE '%widget%'
ORDER BY customerid

--James

 

[lb1]

Thanks,
It is working so much faster!
I notice that even with OR, the more table I add in the JOIN( a total of 4) the less info I retrieve. I assume that I should at least get as much as I get with putting only one table.
I will make some better tests but do you think there is an other reason?
Thanks.
Louis

 

[SQLSister]

The reason why you get less data is that you are using inner joins, this means the record must have an entry in all the tables referenced before it will show up in the data set.
Perhaps what you really need is a union query.

 

[lb1]

to SQLSister
Right, UNION did the trick.
Thanks