CODEWITHSUNDEEP
phpmysql
shi mk
shi mk

Reputation: 5

I cant select data from database with inner join using mysql

I cant select data from database .

My table structrure is given below

customer table

id      name
10      geetha

customer country table

id    cust_id  country

1       10      6
2       10      16

I got the result like these way

customer name      country


geetha             6
geetha             16

But i want to get the one customer data only one time ie with out repeating.

customer name      country


geetha             6

my query is 

SELECT customer.name,customer.id,customer_country.country_id, customer_country.cust_id
    FROM customer
    INNER JOIN customer_country on customer.id = customer_country.cust_id

Upvotes: 0

Views: 151

Answers (3)

Stefan Horvat
Stefan Horvat

Reputation: 63

try this i add distinct before customer_country.cust_id

SELECT customer.name,customer.id,customer_country.country_id, distinct customer_country.cust_id
FROM customer
INNER JOIN customer_country on customer.id = customer_country.cust_id

Upvotes: 0

Biri
Biri

Reputation: 7181

For first record only, apply to the end: limit 1

    SELECT customer.name,customer.id,customer_country.country_id,
    customer_country.cust_id from customer 
    inner join customer_country on customer.id= customer_country.cust_id limit 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you have duplicates in the customer_country table, then you need to choose one of them. Here is one method using max():

select c.name, max(cc.country_id)
from customer c inner join
     customer_country cc
     on c.id = cc.cust_id
group by c.name;

If you want all of them in a list, use group_concat():

select c.name, group_concat(cc.country_id) as countries
from customer c inner join
     customer_country cc
     on c.id = cc.cust_id
group by c.name;

Upvotes: 2

Related Questions