SQL Join query that selects rows and checks if they exist in another table

Question

Not sure if this is possible in a single query but looking to do this directly in SequelPro.

I have a table containing cars, and if they are sold or live.

I also have another table which is called data_store and contains some information about those cars. However, not all of the cars have data in this table.

To get the cars that are live is currently:

SELECT * from cars where `sale_status` = 'live'

I now need it to be like:

SELECT * from cars where `sale_status` = 'live' AND THERE IS A ROW IN THE DATA_STORE TABLE FOR THIS CARS REGISTRATION NUMBER.

There is a registration number field in each of the tables.

Thanks, still getting my head around joins.

looking up some information about join and left join will be very helpful for you. http://www.w3schools.com/sql/sql_join.asp have a read! — user3012759, Feb 18 '15 at 17:32

score 2 · Answer 1 · answered Feb 18 '15 at 17:25

2

You want to do a simple join :

select *
from cars
join data_store on cars.registration_number = data_store.registration_number

change registration_number with whatever is the link with cars in data_store.

This will return only cars containing a row in data_store.

answered Feb 18 '15 at 17:25

Jean-François Savard

20,626
7
49
76

Perfect, I'll look into why this works and what happens. Thanks. – Lovelock Feb 18 '15 at 17:27
I think the join should be a left join since some information might be missing in the data_store table. – A.D. Feb 18 '15 at 17:27
@A.D no, the OP want only cars that are in data_store. – Jean-François Savard Feb 18 '15 at 17:28

SQL Join query that selects rows and checks if they exist in another table

1 Answers1