How to display data from both tables

Question

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

I'm not sure why this is listed as off topic. But there are three problems, not one. First off, you can only have one WHERE clause. Bluefeet (above) and Ollie Jones (below) are both right about that. Also, your where c.area in (select locality , address , area from vendor_home_delivery ) is wrong. That should be c.area in (SELECT area from vendor_home_delivery) or, even better, WHERE EXISTS (SELECT * FROM vendor_home_delivery WHERE c.area = vendor_home_delivery.area). Last, instead of c.phone_number = '9848032919', you want c.phone_number = 9848032919. Note the data type of phone_number — Evan Volgas, Sep 10 '14 at 19:56

score 1 · Answer 1 · answered Sep 10 '14 at 19:14

1

You need

and c.phone_number

in place of

 where c.phone_number

in your query.

answered Sep 10 '14 at 19:14

O. Jones

103,626
17
118
172

Thank you very much ,now i am getting Operand should contain 1 column(s) error . – Pawan Sep 10 '14 at 19:18
2

There are three problems, not one. First off, you can only have one WHERE clause. Bluefeet Ollie Jones are both right about that. Also, your where c.area in (select locality , address , area from vendor_home_delivery ) is wrong. That should be c.area in (SELECT area from vendor_home_delivery). Or, even better, WHERE EXISTS (SELECT * FROM vendor_home_delivery WHERE c.area = vendor_home_delivery.area) (here's why: http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance) Last, instead of c.phone_number = '9848032919', you want c.phone_number = 9848032919 – Evan Volgas Sep 10 '14 at 20:00

M Khalid Junaid · Answer 2 · 2014-09-10T20:02:26.840

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()

How to display data from both tables

2 Answers2