7 Replies - 10458 Views - Last Post: 08 September 2013 - 02:16 AM Rate Topic: -----

#1 DaneAU  Icon User is offline

  • Great::Southern::Land
  • member icon

Reputation: 286
  • View blog
  • Posts: 1,619
  • Joined: 15-May 08

SQL Join For Multiple Rows Return From Sub Query

Posted 04 September 2013 - 10:23 PM

Hi,

I am just having a bit of a task with joining two tables when the results returned from the subquery is not limited to one item. My query is described as follows. To simplify things I would like to just present a hypothetical scenario. Lets say I have two tables

Table - sales
----------------------
id_customer | amount |
----------------------
1           | 2.99   |
2           | 5.88   |
3           | 8.50   |
2           | 3.00   |
----------------------

Table - customers
----------------------
id_customer | name   |
----------------------
1           | "Joe"  |
2           | "Jess" |
3           | "Bec"  |
----------------------



My question is, I would like to iterate through the sales table and find each customer number who has made a transaction.
SELECT DISTINCT id_customer FROM sales;


ResultSet
1
2
3



Next for-each of these id's collected I would like then to pull the customer name. So for instance say the distinct customer_id=2
SELECT name FROM customers WHERE id_customer=2;



ResultSet
"Jess"



I would like to do this all in one query returning a distinct customer number along side the customers name. I thought of doing a JOIN with a sub-query, however am running in to issues with there being multiple rows returned in the subquery. I am not quite sure how to get over this hurdle to produce results that follow below.

ResultSet
----------------------
id_customer | name
----------------------
1           | "Joe"  |
2           | "Jess" |
3           | "Bec"  |
----------------------



My sub-query is taking the following form, however considering this is hypothetical and the fact my sales and customer tables have tens of fields on each I wish to make use of I have simplified this as much as I can.

SELECT customers.name
FROM customers
LEFT JOIN sales
ON customers.id_customer=(
  SELECT DISTINCT id_customer
  FROM sales
);



Obviously this is not going to work as there will be multiple roles returned for the sub-query.

Any help is greatly appreciated.

This post has been edited by DaneAU: 04 September 2013 - 10:23 PM


Is This A Good Question/Topic? 0
  • +

Replies To: SQL Join For Multiple Rows Return From Sub Query

#2 DaneAU  Icon User is offline

  • Great::Southern::Land
  • member icon

Reputation: 286
  • View blog
  • Posts: 1,619
  • Joined: 15-May 08

Re: SQL Join For Multiple Rows Return From Sub Query

Posted 04 September 2013 - 11:38 PM

I believe I have worked this out myself, still a little bug as it is not always returning distinct customer numbers oddly.

SELECT DISTINCT sales.id_customer, customers.name
FROM sales s
JOIN
customers c 
ON c.id_customer=s.id_customer;



// Where clause added
SELECT DISTINCT sales.id_customer, customers.name
FROM sales s
JOIN
customers c 
ON c.id_customer=s.id_customer
WHERE s.amount > 5;


Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5929
  • View blog
  • Posts: 12,851
  • Joined: 16-October 07

Re: SQL Join For Multiple Rows Return From Sub Query

Posted 05 September 2013 - 07:54 AM

Avoid distinct.

You have made perfectly good aliases; use them:
SELECT c.id_customer, c.name, s.amount
	FROM sales s
		INNER JOIN customers c 
			ON c.id_customer=s.id_customer



But you want only one line per customer. Wouldn't it be nice if we could also have some kind of amount listed?

Try:
SELECT c.id_customer, c.name, sum(s.amount) as total
	FROM sales s
		INNER JOIN customers c 
			ON c.id_customer=s.id_customer
	GROUP BY c.id_customer, c.name



Now... you want customers with a total greater than a given amount, you need to use a function that is group by aware.

Read up on group by. Also, the function you'd want is HAVING. See how far you can get from there.

This post has been edited by andrewsw: 05 September 2013 - 04:58 PM

Was This Post Helpful? 3
  • +
  • -

#4 DaneAU  Icon User is offline

  • Great::Southern::Land
  • member icon

Reputation: 286
  • View blog
  • Posts: 1,619
  • Joined: 15-May 08

Re: SQL Join For Multiple Rows Return From Sub Query

Posted 05 September 2013 - 04:27 PM

Hey thanks baavgai, I am making use of my aliases now, however perhaps my example did not really show what it is am trying to achieve.

Although my sales table shows columns for customer_id and amount, what I am actually trying to determine is each customer_id that has made a purchase. I am not interested at this time in the amount purchases, just whether or not one has been made. From this then I would like to get from the customers table some extra fields such as email addresses and mobile phone numbers and compile a list of active customers to interact directly with.

So this is the reason why I believed DISTINCT customer_id would be suitable as I do not wish to return multiple rows with the same customer ID.

Does that make sense at all ? For the sake of ease, below is the real query that I am working on with my Restful Web Services implementation for an API deployed on Glassfish to return JSON/XML formatted data.

SELECT DISTINCT t.id_cust, c.customer_name, c.ph_mobile, c.email
FROM transactions t
JOIN customers c
ON c.id_cust=t.id_cust;



Then I am looking at doing a query for those customers who have made transactions and have opted in for communication.
SELECT DISTINCT t.id_cust, c.customer_name, c.ph_mobile, c.email
FROM transactions t
JOIN customers c
ON c.id_cust=t.id_cust
WHERE c.recieves_communication=1;



Does this make sense?

This post has been edited by DaneAU: 05 September 2013 - 04:27 PM

Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5929
  • View blog
  • Posts: 12,851
  • Joined: 16-October 07

Re: SQL Join For Multiple Rows Return From Sub Query

Posted 05 September 2013 - 04:43 PM

Yep, a DISTINCT makes sense for this instance; just not where you're using it.

Your problem has two parts. First, all customers that have transactions:
SELECT DISTINCT id_cust FROM transactions



There's your list of ids. Now, you want all customers that match those ids. That's a join:
SELECT a.*
	FROM customers a
	INNER JOIN (SELECT DISTINCT id_cust FROM transactions) b
		ON a.id_cust=b.id_cust;


Was This Post Helpful? 1
  • +
  • -

#6 DaneAU  Icon User is offline

  • Great::Southern::Land
  • member icon

Reputation: 286
  • View blog
  • Posts: 1,619
  • Joined: 15-May 08

Re: SQL Join For Multiple Rows Return From Sub Query

Posted 05 September 2013 - 05:11 PM

Thanks again, the inner join is a much nicer solution. Just one quick question further if I may, say the transactions table stores a docket_number, if i were wanting to retrieve that value would it be safe to do.
SELECT c.id_cust, c.name_customer, s.docket_number
FROM customers c
INNER JOIN (
  SELECT DISTINCT id_cust, docket_number
  FROM sales
) s
ON c.id_cust=s.id_cust;


Was This Post Helpful? 0
  • +
  • -

#7 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5929
  • View blog
  • Posts: 12,851
  • Joined: 16-October 07

Re: SQL Join For Multiple Rows Return From Sub Query

Posted 06 September 2013 - 07:11 AM

Not really; you'd run the risk of multiple entries, again.

You could decide to show the first docket number. e.g.
SELECT id_cust, min(docket_number) as first_docket_number, count(distinct docket_number) as dockets
  FROM sales
  GROUP BY id_cust


Was This Post Helpful? 1
  • +
  • -

#8 DaneAU  Icon User is offline

  • Great::Southern::Land
  • member icon

Reputation: 286
  • View blog
  • Posts: 1,619
  • Joined: 15-May 08

Re: SQL Join For Multiple Rows Return From Sub Query

Posted 08 September 2013 - 02:16 AM

Yep I see what you mean baavgai, makes sense and for what I wish to achieve what I have is enough for now.

Thanks ever so much for the guidance and support.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1