CODEWITHSUNDEEP
mysqlsql
user3201500
user3201500

Reputation: 1618

Fetch data from more then 5 tables from UserID common in MySQL

we are trying to build an application using PHP and MYSQL here we have more then 5 different category wise table like tblcategory1, tblcategory2, tblcategory3 and so on.

And each of the category table have a common userID field in all the tables.

We want to check in all the tables what are the entries we have with that particular userID.

Is there any query to checking from multiple tables?

Thank you (in advance)

Upvotes: 0

Views: 29

Answers (2)

MatBailie
MatBailie

Reputation: 86765

An alternative structure to @LaurenceFrost answer is to use UNION ALL to bring the tables together, then filter that result.

SELECT
    columnName
FROM
(
    SELECT userID, columnName FROM tblcategory1
    UNION ALL
    SELECT userID, columnName FROM tblcategory2
    UNION ALL
    SELECT userID, columnName FROM tblcategory3
    UNION ALL
    SELECT userID, columnName FROM tblcategory4
    UNION ALL
    SELECT userID, columnName FROM tblcategory5
)
   ilvCategoryAll
WHERE
   userID = 1

This layout becomes much more friendly when you have JOINs and other business logic, which you don't really want to have to repeat for every source table.

Also, it should be noted that I used UNION ALL rather than UNION. This is because UNION expressly removes duplicates, which can be an expensive process (even if there are no duplicates to find). UNION ALL, however, does not do this de-duplication and is significantly lower cost.

Note: the ilv in ilvCategoryAll means "in-line-view".

Upvotes: 1

Laurence Frost
Laurence Frost

Reputation: 2993

Yes you need to look at the UNION keyword. It roughly works like this:

SELECT  columnName
FROM    category1
WHERE   userID = 1

UNION

SELECT  columnName
FROM    category2
WHERE   userID = 1

UNION

SELECT  columnName
FROM    category2
WHERE   userID = 1

This is essentially 3 separate queries which all run as one, and the results are all joined together to form one list of results.

For more info: UNION

Upvotes: 2

Related Questions