Simple Query Help
-
I apologize if this is oversimplistic for the forum, but I am an old xBase programmer trying to replicate some programs in SQL statements and have one I am struggling with.
I am trying to (in SQL only, without having to write VB or other code) perform a query on a simple table.
The table contains a few columns, but for the sake of this example I will say a table sales has only two columns, name and city. For example:
sales.name sales.city
smith new york
smith chicago
smith detroit
jones new york
jones chicago
simms chicago
I am trying to write an sql statment which will allow me to:
1) Determine for a given employee what other employees have sales in the same city. In the example above, if jones was the subject, smith would be the match.
2) Determine which employees have sales in all of the cities we service, without a predefined list of service. I.E. the query would determine NY, Det, and Chicago are the three cities and smith is the only employee who services all three.
Thanks for any suggestions!
ST
name
-
hai dt,
here is the query for your first case,
select s2.name,s2.city
from sales s1,sales s2
where s1.name='Jones'
and s1.city=s2.city
try it..
regards,
Biju.
-
Biju,
Thanks for the idea. That partially works by generating a list of all of the names which match one or more cities. However, if I wanted to see only those names who have sales in ALL of the cities that another employee does, could this query be modified to do that. For example, in the case of "jones", only "smith" does business in all of the cities "jones" does.
Thanks again, I am slowly learning...
ST
-
1.
SELECT s2.[name]
FROM [sales] s
INNER JOIN [sales] s2 ON s2.city = s.city AND s2.[name] <> s.[name]
WHERE s.[name] = 'jones'
GROUP BY s2.[name]
HAVING COUNT(*) = (SELECT COUNT(*) FROM [sales] s WHERE s.[name] = 'jones')
2.
SELECT s.[name]
FROM (SELECT DISTINCT city FROM [sales]) c
INNER JOIN [sales] s ON s.city = c.city
GROUP BY s.[name]
HAVING COUNT(*) = (SELECT COUNT(DISTINCT city) FROM [sales])
Far away is close at hand in the images of elsewhere.
Anon. -
David,
Thanks so much for your help, both queries work perfectly! Now I am working on figuring out the how and why they work and getting my mind around some of the SQL commands I am not yet familiar with (I'm not yet comfortable with INNER JOIN and had nevered used HAVING COUNT before).
Thanks again, I appreciate it!
-
Check out the BOL for information on the commands.
INNER JOIN means that there must be a match in BOTH tables.
TableL TableR
SSN FName SSN LName
123 Bill 123 Smith
125 Joan 126 Jett
SELECT *
FROM TableL L
INNER JOIN TableR R
ON L.SSN = R.SSN
Will return:
SSN FName LName
123 Bill Smith
Outer Joins return EVERYTHING from the 'identified' table (left or right) and 1) the matches from the second table and 2) NULL where there aren't any matches.
SELECT *
FROM TableL L
LEFT OUTER JOIN TableR R
ON L.SSN = R.SSN
Will return, everything from TableL and the match or NULL from TableR:
SSN FName LName
123 Bill Smith
125 Joan NULL
A RIGHT OUTER JOIN is the opposite...everything from the Right table and matches or NULLs from the Left table:
SSN FName LName
123 Bill Smith
125 NULL Jett
-SQLBill
-
Thanks Bill,
I had just finished re-reading the primer on Join statements and found your examples to be a great addendum to what I just read.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply