CODEWITHSUNDEEP
mysqlsql
west
west

Reputation: 25

Select data from table with compare two table

I have two table.

table one name list

ID  device_id 

1      2

2      2

3      4

Table two name sync

ID  devide_id  list_id

1      4         2

Now if i want to select all data from list table for devide_id then two row shown cause sync table devide_id 4 have a item.

I am trying this query

SELECT s.*
FROM list AS s,sync AS sc
WHERE s.device_id = 4 AND sc.device_id=4 AND sc.list_id=s.id

But Not Work

expected result

For device_id 4

ID  device_id 

 3      4

 2      2

For device_id 2

ID  device_id 

1      2

2      2

Upvotes: 0

Views: 71

Answers (2)

Taryn East
Taryn East

Reputation: 27757

The only way I can see to get your first example is to use something like this:

SELECT s.*
FROM list AS s,sync AS sc
WHERE s.device_id = 4 OR (sc.device_id=4 AND sc.list_id=s.id)

(Note: not tested, may need tweaking)

because your example out put has either device-id of 4 OR it gets the device-id of 4 from the sync table.

Upvotes: 1

KingFeming
KingFeming

Reputation: 1091

The way you have asked the question is bit hard to figure out. The way I understood is,

You want to retrieve all the data from table List having a condition on device_id

But there will be just one record will be displayed according to your table.

If it's possible please revise the question so that we can understand it easily.

Upvotes: 1

Related Questions