CODEWITHSUNDEEP
mysql
Pawan
Pawan

Reputation: 32321

How to display data from both tables

mysql> desc customer_delivery_loc;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| cust_loc_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| customer_id   | int(11)      | YES  | MUL | NULL    |                |
| locality      | varchar(100) | YES  |     | NULL    |                |
| area          | varchar(100) | YES  |     | NULL    |                |
| address       | varchar(100) | YES  |     | NULL    |                |
| city          | varchar(50)  | YES  |     | NULL    |                |
| state         | varchar(50)  | YES  |     | NULL    |                |
| phone_number  | bigint(20)   | YES  |     | NULL    |                |
| +---------------+--------------+------+-----+---------+----------------+



mysql> desc vendor_home_delivery;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| vendor_id | varchar(50)  | YES  | MUL | NULL    |       |
| locality  | varchar(100) | YES  |     | NULL    |       |
| area      | varchar(100) | YES  |     | NULL    |       |
| address   | varchar(100) | YES  |     | NULL    |       |
| city      | varchar(50)  | YES  |     | NULL    |       |
| state     | varchar(50)  | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

I have got two Tables as shown above

The area column is common for both the tables .

From User Interface area and phone_number will be passed .

How to write a query to display the data from both the tables based on phone_number and area ??

I tried this way but i am not sure and its giving error

select c.phone_number 
from customer_delivery_loc c 
where c.area in (select locality , address , area from vendor_home_delivery ) 
where c.phone_number = '9848032919';

- MySQL Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where c.phone_number = '9848032919'' at line 1

Upvotes: 0

Views: 43

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

As one mistake pointed by already by @Ollie Jones for another error Operand should contain 1 column(s) error you can rewrite your query by using join

select c.phone_number 
from customer_delivery_loc c 
join vendor_home_delivery  v 
 on(c.area = v.locality or c.area=v.address or c.area=v.area)
where c.phone_number = '9848032919';

Cause of this error is because you are comparing single column area with 3 columns using in()

Upvotes: 1

O. Jones
O. Jones

Reputation: 108641

You need 

and c.phone_number

in place of

 where c.phone_number

in your query.

Upvotes: 1

Related Questions