Helpex - Trao đổi & giúp đỡ Đăng nhập
9

Hi Can someone help me about sql

i have this data in my Table:

date_added | location     |  status
2012-08-01    Manila           1
2012-08-01    Japan            1
2012-08-01    Cebu             1

2012-08-04    Manila           1
2012-08-04    Cebu             1
2012-08-04    Africa           1

2012-08-06    Manila           1
2012-08-06    Japan            1
2012-08-06    Cebu             1


how can i get the date_added result with the location in 'Manila' , 'Japan', 'Cebu' and has status =1

The three data must exist before i can get the date.

Result should be: based on this table

date_added 
 2012-08-01
 2012-08-06

since on 2012-08-04 'Japan' did not exists.

My Current SAMPLE SQL:

SELECT date_added FROM TABLE WHERE location ='Manila' AND location ='Japan' 
       AND location ='Cebu' AND STATUS =1;

Please help.....any help will greatly appreciated

9 hữu ích 0 bình luận 2.2k xem chia sẻ
11

try this:

SELECT DATE_ADDED 
FROM TABLE 
WHERE LOCATION IN ('MANILA' , 'JAPAN' , 'CEBU')
AND STATUS =1
GROUP BY DATE_ADDED
HAVING (COUNT(DISTINCT LOCATION)=3)
11 hữu ích 0 bình luận chia sẻ
2

You need the SQL IN operator:

SELECT date_added FROM TABLE WHERE location IN ('Manila', 'Japan', 'Cebu') AND STATUS =1;

Or alternatively, you need to use the OR operator and bracket the location statements:

SELECT date_added FROM TABLE WHERE ( location ='Manila' OR location ='Japan' OR location ='Cebu' ) AND STATUS =1;
2 hữu ích 1 bình luận chia sẻ
0

Try this

SELECT date_added FROM TABLE WHERE location ='Manila' or location ='Japan'  or location ='Cebu' AND STATUS =1;

or

select date_added FROM TABLE WHERE location in ('Manila','Japan','Cebu') AND status='1' GROUPED BY date_added
0 hữu ích 3 bình luận chia sẻ
0
WHERE location IN ('Manila' , 'Japan', 'Cebu') AND status = 1
0 hữu ích 0 bình luận chia sẻ
0

when you use AND condition on the same field,it will always result to false because a field can only have 1 possible value. So you needto use IN for that

SELECT Date_added 
FROM   myTable
WHERE  `Location` IN ('MANILA' , 'JAPAN' , 'CEBU')
          AND `Status` =1
GROUP BY Date_added 
HAVING   COUNT(DISTINCT Location) = 3
0 hữu ích 0 bình luận chia sẻ
loading
Không tìm thấy câu trả lời bạn tìm kiếm? Duyệt qua các câu hỏi được gắn thẻ php mysql sql , hoặc hỏi câu hỏi của bạn.

Có thể bạn quan tâm

loading