how to fetch data from two tables

Question

I have two tables

table A

  imei_no | user_name | date_of_allocation | date_of_deallocation
     123  |    a      |      1-4-2013      |         10-4-2013         
     456  |    a      |     10-4-2013      |         25-4-2013
     789  |    a      |     25-4-2013      |         30-4-2013
     123  |    b      |     25-4-2013      |         27-4-2013

table B

  imei | lat | long |     gpsdate
   123 | 23  |  43  |     2-4-2013
   123 | 23  |  43  |     2-4-2013
   456 | 23  |  43  |     3-4-2013
   123 | 23  |  43  |     3-4-2013
   789 | 23  |  43  |     24-4-2013
   123 | 23  |  43  |     24-4-2013
   456 | 23  |  43  |     28-4-2013
   123 | 23  |  43  |     28-4-2013

I want imei of a particular user say a from date 5-4-2013 to 25-4-2013 I am doin this:

select imei from A where user_name='a' and date_of_allocation>='5-4-2013' and date_of_deallocation<='25-4-2013';

and I want the data of that user from table B. how can I do that.

select Imei of user a from some date to some date,then data of that imei for that user from table B

[How can an SQL query return data from multiple tables?](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) — DCoder, Aug 10 '13 at 12:08

score 0 · Answer 1 · answered Aug 10 '13 at 12:10

0

You can use a subquery in the form

SELECT * FROM B WHERE imei IN ( SELECT imei_no FROM A WHERE .... )

You can also use joining

SELECT * FROM A LEFT JOIN B ON (A.imei_no = B.imei) WHERE ...

http://dev.mysql.com/doc/refman/5.0/en/join.html

There are many possible options, depending on your particular application, size of tables etc.

answered Aug 10 '13 at 12:10

lejlot

64,777
8
131
164

I did 1st one but in that case there is also data of b in table B for that imei – user2500442 Aug 10 '13 at 12:34
of course there is such data - how could you distinguish between a and b if they have identical imei? You forgot to add, that you also want gpsdate to be used for filtering I guess – lejlot Aug 10 '13 at 12:55

score 0 · Answer 2 · answered Aug 10 '13 at 12:10

0

Try like

SELECT A.*,B.lat,B.long,B.gpsdate 
FROM A
JOIN B 
    ON A.imei_no = B.imei
WHERE user_name='a' AND
      date_of_allocation >= '5-4-2013' AND
      date_of_deallocation <= '25-4-2013'

answered Aug 10 '13 at 12:10

GautamD31

28,552
10
64
85

score 0 · Answer 3 · answered Aug 10 '13 at 12:14

0

Something like

select * from B where imei in 
    (select imei_no from A where username='a' and ...)

Or you can use joins:

select * from A left join B on (A.imei_no = B.imei) where A.username='a' and ...

answered Aug 10 '13 at 12:14

qsys

176
2
8

how to fetch data from two tables

3 Answers3