CODEWITHSUNDEEP
mysqlsql
gtcode
gtcode

Reputation: 57

SQL search no duplicates

Ok I'm trying to learn the best way to perform a search. I have 40 tables. In the 40 tables All tables have 2 of the same column, ID and date; all the tables obviously have other columns that don't match. Now not every table is used by the user. A user could use 5 tables, they could use all 40. Also the IDs could have multiple dates attached too them. I would like to get ALL the dates from all 40 tables from an ID with no duplicates. AKA if table 1 and table 10 have the same date I only want it 1 time.

So here I have some questions.

The first way I have read is this.

 $stmt = $this->con->prepare("SELECT date FROM table1 
 left join table2 on table1.id = table2.id
 left joins for table2--->table40
 WHERE ID = ?
 GROUP BY date");

the second way I have seen is like this.

 $stmt = $this->con->prepare("SELECT Distinct date FROM table1 
 left join table2 on table1.id = table2.id
 left joins for table2--->table40
 WHERE ID = ?");

and the third way I have read is this. (their words not mine) "In my experience (which is limited btw), I have used the equi join query for this purpose. Let me show you an example.

Consider three tables named Table1, Table2, and Table3 with a common column named Code(turned to ID). The code snippet below joins these three tables with no duplicates:"

 select date from Table1, Table2, Table3..... where Table1.ID=Table2.ID=Table3.ID......;

Now my question is what is the proper way for me to do this search??

It should return a list of dates found for 1 ID (the one being searched) no duplicates.

Upvotes: 1

Views: 51

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726639

Your first and third approach differ only on syntax (first approach has a proper syntax, while the third one uses an archaic syntax that shouldn't be used for any new development for the last 15+ years).

All three approaches suffer from the same problem: when Table1 does not have a target ID, presence or absence of that ID in other tables would not matter: you wouldn't get any dates from them unless Table1 has the target ID.

You can fix this by using UNION on individual selects, like this:

SELECT date
FROM (
    SELECT date FROM Table1 WHERE Id=?
UNION
    SELECT date FROM Table2 WHERE Id=?
UNIO
    SELECT date FROM Table3 WHERE Id=?
UNION
    ...
UNION
    SELECT date FROM Table40 WHERE Id=?
) AS x

This would yield all date values regardless of the table in which it is found.

Note: An unfortunate side effect of this approach is the need to bind Id parameter forty times, because JDBC does not support named parameters.

Upvotes: 2

Related Questions