CODEWITHSUNDEEP
phpmysqlsql
Dan
Dan

Reputation: 829

Finding matching records from two tables MySQL

I have two tables book1 and book2. I have issue_datecolumn in both the tables. I want to get the records from both tables for matching issue date.

book1 and book2 have the following columns -

book_id | user_id | numofbooks | issue_date

I tried this SQL query given below but doesn't work -

SELECT * FROM `book1`, `book2` WHERE issue_date between '2014-04-06' and '2014-05-07';

Throws an error. Is it possible to do it SQL way or should i query both the tables separately and merge the result sets?

Upvotes: 1

Views: 980

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I doubt that you really want a cartesian product of all matching records from the two tables.

I think you want something like this:

SELECT *
FROM book1
WHERE issue_date between '2014-04-06' and '2014-05-07'
UNION ALL
SELECT *
FROM book2
WHERE issue_date between '2014-04-06' and '2014-05-07';

This assumes that the two tables have exactly the same columns defined in the same order (and that, in turn, would suggest a bad database design). In practice, you would replace the * with the columns you really want.

Upvotes: 4

wvdz
wvdz

Reputation: 16651

Use UNION ALL:

SELECT * FROM book1
WHERE issue_date between '2014-04-06' and '2014-05-07'
UNION ALL
SELECT * FROM book2
WHERE issue_date between '2014-04-06' and '2014-05-07';

This will only work if book1 and book2 have the same columns in the same order, in which case it's questionable why this wasn't designed as 1 table in the first place.

If the columns don't match exactly you can replace * with the relevant column names.

Upvotes: 2

Dipak
Dipak

Reputation: 493

you can apply Union All feature as mentioned above but you should have the same name of the fields in both the tables book1 & book2

Upvotes: 2

Related Questions