CODEWITHSUNDEEP
sqljoin
Cur123
Cur123

Reputation: 89

sql join issue - not giving expected output

I am a SQL noob and so this might be pretty basic to you all, but I am struggling to apply some logic here. I am trying to produce a list of all cities and states from 2 tables(suppliers vs consumers tables),linked by a 3rd transact table, and list the number of suppliers and consumers in each city(grouped by). Point to be noted is that the number of cities and state dont match in the 2 tables. For those that match, I need an output showing corresponding number of suppliers and consumers, if the state/city is present in one table and not the other, I need a number count of zero displayed against it under the corresponding number column. I have 3 tables- Supplier, Consumer and Transaction linked by Supp_ID and Con_ID as keys, as mentioned in my code below. 

SELECT S.State, S.City, COUNT(S.Supp_ID) AS Supp_Count, COUNT(C.Con_ID) AS Cust_Count 
FROM SuppTb S, TransTb T, ConsTb C
WHERE S.Supp_ID = T.Supp_ID AND T.Con_ID = C.Con_ID
GROUP BY S.State, S.City

If I input this query, I am getting weird numbers against the number count of suppliers and consumers. My best guess is that there has to be a self join somewhere in there, but not sure how to take this from here. Any help would be appreciated! Thank u

PS:

SELECT DISTINCT City, State, COUNT(Supp_ID)
FROM SuppTb S GROUP BY City, State

and

SELECT DISTINCT City, State, COUNT(Con_ID)
FROM ConTb C GROUP BY City, State

gives me the output I need from both tables separately. Output 1 has 3 columns and 7 rows, Output 2 has 3 columns and 6 rows. I need one single combined output with 4 columns and 8 rows. Just need to figure out a way to combine these 2 outputs and insert zeroes wherever needed.

Edit- The exact question is to generate a comparative list of supplier or consumer states and cities with respective number of suppliers and consumers in each city. Desired output-

 State       City      Supp_count       Cons_count
 Illinois    Chicago      2                 3
 Illinois  Springfield    2                 0
 Michigan   Lansing       0                 3

etc i.e., 0 for output if supp/cons table does not have the city/state listed.

Edit 2- Tried this code, gave me desired counts on customer count but gives weird numbers on Supplier count. I am totally stuck now! Running out of ideas..

SELECT C.State, C.City, COUNT(DISTINCT S.Supp_ID) AS Supp_Count,      COUNT(DISTINCT C.Con_ID) AS Cust_Count 
FROM Tb_Supplier S LEFT JOIN Tb_Transactions T
ON S.Supp_ID = T.Supp_ID LEFT JOIN
    Tb_Consumer C ON T.Con_ID = C.Con_ID
    GROUP BY C.State, C.City
 EXCEPT
 SELECT S.State, S.City, COUNT(DISTINCT S.Supp_ID) AS Supp_Count,    COUNT(DISTINCT C.Con_ID) AS Cust_Count 
 FROM Tb_Supplier S LEFT JOIN Tb_Transactions T
 ON S.Supp_ID = T.Supp_ID LEFT JOIN
    Tb_Consumer C ON T.Con_ID = C.Con_ID
 GROUP BY S.State, S.City

Upvotes: 1

Views: 470

Answers (2)

Cur123
Cur123

Reputation: 89

Update- I got the answer myself!! I first created a new table to list all the DISTINCT states and cities that I was interested in (used UNION instead of UNION ALL to eliminate the dups I was getting earlier), then used FULL JOINs to link and combine the supplier and consumer tables to this new table.

Ahhh! The joys of getting a query right all by yourself when you are completely new to the programming language is just...beyond compare!! Thank you!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

First, never use commas in the FROM clause. Always use explicit, proper JOIN syntax.

Your problem is that you are joining along two unrelated dimensions, so your query produces a Cartesian product for each state/city combination. The simplest solution is to use COUNT(DISTINCT): 

SELECT S.State, S.City,
       COUNT(DISTINCT S.Supp_ID) AS Supp_Count,
       COUNT(DISTINCT C.Con_ID) AS Cust_Count 
FROM SuppTb S JOIN
     TransTb T
     ON S.Supp_ID = T.Supp_ID JOIN
     ConsTb C
     ON T.Con_ID = C.Con_ID
GROUP BY S.State, S.City;

This works fine, if there are not too many duplicates along each dimensions. The more "proper" solution is to aggregation before doing the joins.

Upvotes: 1

Related Questions