Seems Soo Typical JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Seems Soo Typical JOIN

Author

Topic

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-27 : 03:08:53

I HAVE THREE TABLES
TABLE S(SID, SNAME,ADDRESS)
SID IS PRIMARY KEY
TABLE P(PID,PNAME,COLOR)
PID IS PRIMARY KEY
TABLE CAT(SID,PID,COST)
PRIMARY KEY: SID+PID
REFERENCE KEY : SID REFERENCES S.SID
: PID REFERENCES P.PID
I WANT THE SNAME OF SUPPLIERS WHO SUPPLY EVERY PART
I AM TRYING
[CODE]
SELECT S.SNAME,S.SID,P.PID FROM S INNER JOIN CAT C
ON s.sid=c.sid CROSS JOIN P WHERE P.PID=C.PID
[/CODE]
AFTER THE WHERE WHICH CONDITION I HAD TO PUT IS A LITTLE BIT CONFUSED PLZ HELP

With Regards
Kashyap M

Kristen
Test

22859 Posts

Posted - 2010-10-27 : 03:25:40

Something like this perhaps?


SELECT SupplierID
FROM SupplierProductLink
GROUP BY SupplierID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Products)


SELECT S.xxx
FROM Supplier AS S
WHERE NOT EXISTS
(
    SELECT *
    FROM Products AS P
    WHERE NOT EXISTS
    (
        SELECT *
        FROM   SupplierProductLink AS L
        WHERE  L.ProductID = P.ProductID
               AND L.SupplierID = S.SupplierID
   )
)

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-27 : 03:38:12

THIS IS VERY USEFUL THANKS KRISTEN
IS THERE ANY IDEAS TO CHANGE MODIFICATIONS IN MY CODE BY USING JOINS

With Regards
Kashyap M

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-27 : 03:53:50

quote:
Originally posted by kashyap_sql

THIS IS VERY USEFUL THANKS KRISTEN
IS THERE ANY IDEAS TO CHANGE MODIFICATIONS IN MY CODE BY USING JOINS

With Regards
Kashyap M

What is wrong with given solutions?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-10-27 : 04:08:30

Why do you want to use JOINs? You are going to select thousands of rows that are not needed in the resultset.

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-10-27 : 06:51:15

quote:
Originally posted by madhivanan

quote:
Originally posted by kashyap_sql

THIS IS VERY USEFUL THANKS KRISTEN
IS THERE ANY IDEAS TO CHANGE MODIFICATIONS IN MY CODE BY USING JOINS

With Regards
Kashyap M

What is wrong with given solutions?

Madhivanan

Failing to plan is Planning to fail

there is no wrong with the code it was very simple and very useful
i was just trying to implement my need by using join

With Regards
Kashyap M

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-27 : 08:09:42

quote:
Originally posted by kashyap_sql

I HAVE THREE TABLES
TABLE S(SID, SNAME,ADDRESS)
SID IS PRIMARY KEY
TABLE P(PID,PNAME,COLOR)
PID IS PRIMARY KEY
TABLE CAT(SID,PID,COST)
PRIMARY KEY: SID+PID
REFERENCE KEY : SID REFERENCES S.SID
: PID REFERENCES P.PID
I WANT THE SNAME OF SUPPLIERS WHO SUPPLY EVERY PART
I AM TRYING
[CODE]
SELECT S.SNAME,S.SID,P.PID FROM S INNER JOIN CAT C
ON s.sid=c.sid CROSS JOIN P WHERE P.PID=C.PID
[/CODE]
AFTER THE WHERE WHICH CONDITION I HAD TO PUT IS A LITTLE BIT CONFUSED PLZ HELP

With Regards
Kashyap M

CROSS JOIN means every row in the Left_Table is joined to every row in the Right_Table.
There is no need for a condition.

So what would you like your WHERE clause to do?

No, you're never too old to Yak'n'Roll if you're too young to die.

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-11-01 : 08:25:21

find the pnames of parts supplied by london supplier and by no one else

what it means any suggestions will be very helpful thanks in advance

With Regards
Kashyap M

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-01 : 11:29:19

The best would be if you could provide the DDL, some sample data and the wanted output in relation to the sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-11-02 : 01:37:29

TABLE P LOOKS LIKE


PID.   PNAME.  COLOR
101	AAA	RED
102	BBB	BLUE
103	CCC	GREEN
104	DDD	BLACK
105	EEE	PINK
106	FFF	WHITE
107	GGG	YELLOW

TABLE S LOOKS LIKE
[CODE]
SID. SNAME. ADDRESS
1 ABCD LONDON
2 EFGH NEWYORK
3 IJKL AUSTIN
4 MNOP CALIFORNIA
5 QSTU LONDON
6 VWXY NOIDA
7 ZABC SWEDEN
[/CODE]
TABLE CAT LOOKS LIKE
[CODE]
SID. PID. COST
1 101 781.00
1 102 61.00
1 103 15.00
1 104 401.00
1 105 841.00
1 106 689.00
1 107 581.00
2 107 67.00
3 106 154.00
4 102 5.00
4 104 15.20
5 101 75.00
5 104 92.20
6 105 81.00
7 104 15.60
[/CODE]
I DON'T HAVE ANY IDEA ABOUT THE OUTPUT
i want just the PNAMES of parts supplied by LONDON supplier and by no one else

With Regards
Kashyap M

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-11-02 : 01:38:40

i was trying like


SELECT P.PNAME,S.ADDRESS FROM P JOIN CAT C 
ON P.PID=C.PID INNER JOIN S ON S.SID=C.SID WHERE S.ADDRESS='LONDON' 
GROUP BY P.PNAME,S.ADDRESS

With Regards
Kashyap M

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-02 : 06:51:32

[code]declare @P table (PID int, PNAME varchar(255), COLOR varchar(255))
declare @S table ([SID] int, SNAME varchar(255), [ADDRESS] varchar(255))
declare @CAT table ([SID] int, PID int, COST decimal(12,2))

insert @P
select 101, 'AAA', 'RED' union all
select 102, 'BBB', 'BLUE' union all
select 103, 'CCC', 'GREEN' union all
select 104, 'DDD', 'BLACK' union all
select 105, 'EEE', 'PINK' union all
select 106, 'FFF', 'WHITE' union all
select 107, 'GGG', 'YELLOW'

insert @S
select 1, 'ABCD', 'LONDON' union all
select 2, 'EFGH', 'NEWYORK' union all
select 3, 'IJKL', 'AUSTIN' union all
select 4, 'MNOP', 'CALIFORNIA' union all
select 5, 'QSTU', 'LONDON' union all
select 6, 'VWXY', 'NOIDA' union all
select 7, 'ZABC', 'SWEDEN'

insert @CAT
select 1, 101, 781.00 union all
select 1, 102, 61.00 union all
select 1, 103, 15.00 union all
select 1, 104, 401.00 union all
select 1, 105, 841.00 union all
select 1, 106, 689.00 union all
select 1, 107, 581.00 union all
select 2, 107, 67.00 union all
select 3, 106, 154.00 union all
select 4, 102, 5.00 union all
select 4, 104, 15.20 union all
select 5, 101, 75.00 union all
select 5, 104, 92.20 union all
select 6, 105, 81.00 union all
select 7, 104, 15.60

select DISTINCT
p.PNAME
from @CAT as c
join @S as s
on s.[SID]=c.[SID] and s.[ADDRESS]='LONDON'
join @P as p
on p.PID = c.PID
and not exists(select *
from @CAT c2
where c2.PID=c.PID
and c2.[SID] in(select [SID] from @S where [ADDRESS] <> 'LONDON'))
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-02 : 06:52:42

By the way.
Giving testdata in the above format is your job - not mine!!

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-02 : 06:56:18

This look very much like "Relational division".
See more here http://weblogs.sqlteam.com/peterl/archive/2010/06/30/Relational-division.aspx
and this (which is probably what you want) http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-02 : 07:04:35

Wow - YES!

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-02 : 08:33:33

Hello Peso,
I found that very interesting.
Now I have modified my solution and it would be cool if you could evaluate if it is a right solution regarding "relational division".
Many thanks.

SELECT	p.PNAME
FROM	@CAT AS c
JOIN @S AS s
	ON s.[SID] = c.[SID] 
JOIN @P AS p
	ON p.PID = c.PID
GROUP BY p.PNAME
HAVING	MIN(CASE WHEN s.[ADDRESS] = 'LONDON' THEN 1 ELSE 0 END)=1
	AND SUM(CASE WHEN s.[ADDRESS] <> 'LONDON' THEN 1 ELSE 0 END)=0

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-02 : 08:42:42

Look ok to me. You can even drop the SUM aggregation.

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-02 : 09:11:21

Thank you Peso.
You are right (ah - yes you know that you are right

)

It took me a while to get WHY it works.

This
HAVING MIN(CASE WHEN s.[ADDRESS] = 'LONDON' THEN 1 ELSE 0 END)=1
secures that 'LONDON' exists in the grouping and ONLY 'LONDON' because otherwise the value of MIN() would be zero.

I've learned so much today - thanks again.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-02 : 17:34:48

You're welcome.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources