Get all values from Table1 and get one field's value from Table 2

Question

I have two tables

Table 1 and table 2

I would like to get all the data from Table1 and one column's value from table 2.

Unique details for both tables are the mobile number.

So I tried this:

SELECT d.*, a.paymentmode FROM table1 d JOIN table2 a ON d.user = a.mobile WHERE a.mobile = '+911234567890'

I get double results.

Like: For the above mobile number.

Order1

Order2

Order3

Order4

What is the issue here? How do I show only Order1, Order2, Order3, Order4 instead of double?

Sample data already added.. but only one column. that is all.. — Sanjana Nair, Sep 12 '18 at 07:55

score 0 · Answer 1 · answered Sep 12 '18 at 05:08

0

Assuming Table 1 user column is a mobile number, what you're missing is the type of JOIN

Types of join and their logic can be found here

Btw - Another thing I would do is remove all none numeric keys ('+', '-' etc) from the mobile number fields, it can save possible JOI errors etc.

answered Sep 12 '18 at 05:08

Guy Louzon

Didn't understand what are you trying to say? Please explain thanks! – Sanjana Nair Sep 12 '18 at 08:03
INNER JOIN or LEFT JOIN will give you 2 different answers. please look at the link I added – Guy Louzon Sep 12 '18 at 08:28

score -1 · Answer 2 · answered Sep 12 '18 at 05:07

-1

I think you need to use Group by.

 SELECT t1.*, t2.column_name FROM table1 AS t1 JOIN table2 AS t2 ON t1.mobile = t2.mobile WHERE t2.mobile = '+911234567890' GROUP BY t1.mobile

answered Sep 12 '18 at 05:07

Nashir

You'll lose cases in that way. Under the assumption that the mobile numbers are unique, no duplicates should appear anyway – Guy Louzon Sep 12 '18 at 06:35
If I use this query I am getting only one record. – Sanjana Nair Sep 12 '18 at 08:00

2 Answers2