|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
LEFT JOIN... WHERE vs AND ?Ok... I give up... how do these 2 queries differ?
SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) WHERE (OtherField<>6) SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) AND (OtherField<>6) (The "WHERE" is replaced with "AND") Thanks The problem I have is that, because you didn't bother qualifying what table
OtherField is in, I have at least two different answers. ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message Show quote news:%23CQiEzFyGHA.1252@TK2MSFTNGP04.phx.gbl... > > Ok... I give up... how do these 2 queries differ? > > SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) > WHERE (OtherField<>6) > SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) > AND (OtherField<>6) > > (The "WHERE" is replaced with "AND") > > Thanks > > > The problem I have is that, because you didn't bother qualifying what I should have said... No table I have EVER made has EVER had identical field > table OtherField is in, I have at least two different answers. names. (Not sure why anyone would do that... and make this even MORE confusing.) But I think you've found the problem. It *DOES* matter which table OtherField is in... even though it's only in 1 of the tables (and doesn't really need to be qualified.) Maybe I need to start qualifing *ALL* my fields when joining tables... even though they only appear in 1 of the 2 tables. More.... What I was trying to ultimately do was to list *ALL* the rows in Table1... only EXCLUDING the ones that have a matching field in Table2. List all the rows in Table1 ... EXCEPT where Table1.MyField=Table2.MyField I was thinking it would be as easy as changing this... but it's not: > SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1.MyField = If Table1 contains values like:> Table2.MyField) SELECT * FROM Table1 LEFT JOIN Table2 ON > (Table1.MyField <> Table2.MyField) 2 4 6 8 10 And Table2 contains values like: 1 4 6 The output should be: 2 8 10 (All rows in Table1 are listed... except the matching rows in Table2) I want to thank everyone for the fast replies! Show quote > The problem I have is that, because you didn't bother qualifying what > table OtherField is in, I have at least two different answers. > ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message > news:%23CQiEzFyGHA.1252@TK2MSFTNGP04.phx.gbl... >> >> Ok... I give up... how do these 2 queries differ? >> >> SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) >> WHERE (OtherField<>6) >> SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) >> AND (OtherField<>6) >> >> (The "WHERE" is replaced with "AND") >> >> Thanks >> >> > > > I should have said... No table I have EVER made has EVER had identical Are you serious?> field names. If you have a table called customers: CREATE TABLE dbo.Customers ( CustomerID ... Then you have a table called Orders, which contains a foreign key reference: CREATE TABLE dbo.Orders ( ..., CustomerID ... FOREIGN KEY REFERENCES dbo.Customers(CustomerID)... What else would you call that CustomerID column? Please don't tell me you would call Customers.ID instead of Customers.CustomerID. They represent the same entity, and should not have different names. If you have a table of States that drive a picklist so you don't have people typing in Gorja and Kantukkee, presumably you would have a table called States, with a StateID column and a StateName column, perhaps. So if you put StateID in multiple other tables (such as customer's demographics, order shipment address, manufacturer's routes, etc), you would make up a different name for the column StateID for each table? > Maybe I need to start qualifing *ALL* my fields when joining tables... I always do. Using aliases cuts down on carpal. e.g.> even though they only appear in 1 of the 2 tables. SELECT t1.MyField -- never use SELECT * in production code! FROM dbo.Table1 t1 LEFT OUTER JOIN dbo.Table2 t2 ON t1.MyField = t2.MyField WHERE t2.MyField IS NULL; A I would start all the field-names in the customer-table with the name "Cust"
.... and all the fields in the Order table with "Ord". So the fields would be called: CustUserId and OrdUserId I would think this would help *AVOID* all confusion between the 2. (Fully qualified or not... I instantly know which table the fields are in... and what they refer to... and the similar names tell me they joined keys.) None of which can be done if you call 2 (or 102) different fields... all by the same name. Imagine 102 fields all with identical names. It's interesting how "1 person's confusing nightmare"... is another person's "I do that to keep things clear". More...... > SELECT Your example works... then it breaks if I try to exclude a few addition > t1.MyField -- never use SELECT * in production code! > FROM > dbo.Table1 t1 > LEFT OUTER JOIN > dbo.Table2 t2 > ON t1.MyField = t2.MyField > WHERE t2.MyField IS NULL; records from being listed. > WHERE (t2.MyField IS NULL) AND (t2.AnotherField = 34) If AnotherField in Table2 is 34... then also avoid listing the matching records in Table1. I *DO* appreciate everyone's help with this. You are a lifesaver! Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%23OzQuGHyGHA.4240@TK2MSFTNGP03.phx.gbl... >> I should have said... No table I have EVER made has EVER had identical >> field names. > > Are you serious? > > If you have a table called customers: > > CREATE TABLE dbo.Customers > ( > CustomerID ... > > Then you have a table called Orders, which contains a foreign key > reference: > > CREATE TABLE dbo.Orders > ( > ..., > CustomerID ... FOREIGN KEY REFERENCES dbo.Customers(CustomerID)... > > What else would you call that CustomerID column? Please don't tell me you > would call Customers.ID instead of Customers.CustomerID. They represent > the same entity, and should not have different names. > > If you have a table of States that drive a picklist so you don't have > people typing in Gorja and Kantukkee, presumably you would have a table > called States, with a StateID column and a StateName column, perhaps. So > if you put StateID in multiple other tables (such as customer's > demographics, order shipment address, manufacturer's routes, etc), you > would make up a different name for the column StateID for each table? > >> Maybe I need to start qualifing *ALL* my fields when joining tables... >> even though they only appear in 1 of the 2 tables. > > I always do. Using aliases cuts down on carpal. e.g. > > SELECT > t1.MyField -- never use SELECT * in production code! > FROM > dbo.Table1 t1 > LEFT OUTER JOIN > dbo.Table2 t2 > ON t1.MyField = t2.MyField > WHERE t2.MyField IS NULL; > > A > >I would start all the field-names in the customer-table with the name Ugh, sorry, but that is just way too complex and verbose. A user is a user >"Cust" ... and all the fields in the Order table with "Ord". > So the fields would be called: CustUserId and OrdUserId and has a UserID. Their UserID is not different no matter which table it is stored in. > I would think this would help *AVOID* all confusion between the 2. If you're returning a JOIN, you shouldn't need to know which table the column is in. You shouldn't be returning two columns in the join that contain the same data. And you shouldn't have to wonder whether you should use CustUserID or OrdUserID or ChangePasswordLogUserID. Have you ever actually tried to write joins using your wacky column names? Or does your schema consist of only two or three tables, so it hasn't been a problem yet? Anyway, do what you want. But this kind of mentality would never last in our shop. > similar names tell me they joined keys.) None of which can be done if you Again, why you would need to return 102 copies of the "same" column in a > call 2 (or 102) different fields... all by the same name. query, I'd love to hear an explanation for that. I only need to know the CustomerID once. A "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message Writing OrdUserId instead UserId is *WAY* too complex and verbose? (3 extra news:%23Azpj7HyGHA.540@TK2MSFTNGP03.phx.gbl... > >I would start all the field-names in the customer-table with the name > >"Cust" ... and all the fields in the Order table with "Ord". >> So the fields would be called: CustUserId and OrdUserId > > Ugh, sorry, but that is just way too complex and verbose. letters) (And it saves writing MyFullTableName.UserId and MyOtherFullTableName.UserId many times over.) Like I said: 1 person's "that makes 100% clearer" is another person's "that makes it too complex and verbose". I *LIKE* the fact that everyone has their own methods for "clarity"! It's actually very interesting and good to know "how the other guys do it".
Show quote
"A_Michigan_User" wrote: Which is what aliases are for ... think "t1" instead of "MyFullTableName"> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:%23Azpj7HyGHA.540@TK2MSFTNGP03.phx.gbl... >>> I would start all the field-names in the customer-table with the >>> name "Cust" ... and all the fields in the Order table with "Ord". >>> So the fields would be called: CustUserId and OrdUserId >> >> Ugh, sorry, but that is just way too complex and verbose. > > Writing OrdUserId instead UserId is *WAY* too complex and verbose? (3 > extra letters) > > (And it saves writing MyFullTableName.UserId and > MyOtherFullTableName.UserId many times over.) -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message
news:%23EJbD3HyGHA.4024@TK2MSFTNGP02.phx.gbl... <snip>Show quote >> SELECT If you want to do that, then use:>> t1.MyField -- never use SELECT * in production code! >> FROM >> dbo.Table1 t1 >> LEFT OUTER JOIN >> dbo.Table2 t2 >> ON t1.MyField = t2.MyField >> WHERE t2.MyField IS NULL; > > Your example works... then it breaks if I try to exclude a few addition > records from being listed. >> WHERE (t2.MyField IS NULL) AND (t2.AnotherField = 34) > > If AnotherField in Table2 is 34... then also avoid listing the matching > records in Table1. > > I *DO* appreciate everyone's help with this. You are a lifesaver! SELECT t1.MyField -- never use SELECT * in production code! FROM dbo.Table1 t1 LEFT OUTER JOIN dbo.Table2 t2 ON t1.MyField = t2.MyField AND t2.AnotherFIeld = 34 WHERE t2.MyField IS NULL; Tom <snip> Tom,
Here's some more info: -- Table1 is a list of employees, colors, and whether this color can still be voted on or not IF(OBJECT_ID('dbo.Table1') IS NOT NULL) DROP TABLE dbo.Table1 GO CREATE TABLE dbo.Table1 ( Table1Emp Int NOT NULL DEFAULT 0, Table1Color VarChar(22) NOT NULL DEFAULT '', Table1Decided SmallInt NOT NULL DEFAULT 0 ) GO INSERT INTO Table1 VALUES(2 , 'Red' , 0) INSERT INTO Table1 VALUES(4 , 'Green' , 0) INSERT INTO Table1 VALUES(6 , 'Red' , 0) INSERT INTO Table1 VALUES(8 , 'Blue' , 0) INSERT INTO Table1 VALUES(10, '' , 0) INSERT INTO Table1 VALUES(12, 'Purple', -1) GO -- Table2 is a list of employees, the color they voted for, and their comments IF(OBJECT_ID('dbo.Table2') IS NOT NULL) DROP TABLE dbo.Table2 GO CREATE TABLE dbo.Table2 ( Table2Emp Int NOT NULL DEFAULT 0, Table2Color VarChar(22) NOT NULL DEFAULT '', Table2Vote VarChar(22) NOT NULL DEFAULT '' ) GO INSERT INTO Table2 VALUES(2, 'Blue' , 'Hate it' ) INSERT INTO Table2 VALUES(2, 'Red' , 'Love it' ) INSERT INTO Table2 VALUES(4, 'Blue' , 'Too loud') INSERT INTO Table2 VALUES(4, 'Green', 'Too dark') GO select * from Table1 select * from Table2 -- Show the rows in Table1 that employee #4 can still vote on (THIS CODE DOES NOT WORK) SELECT *,Table1Emp,Table2Emp FROM Table1 LEFT JOIN Table2 ON (Table1Emp = Table2Emp) WHERE NOT ( (Table2Emp IS NULL) OR -- His vote is NOT found in Table2 (Table1Emp=4) OR -- He can't vote for colors if he was the 1 that suggested it originally (Table1Color='') OR -- He can't vote on colors that are "" (Table1Decided=0) OR -- He can't vote on colors that have already been decided (voting closed) ((Table1Emp=4) AND (Table2Emp=4)) -- He can't vote on colors that he's already voted on ) Show quote "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message news:H-GdnfwZDeBxyXLZnZ2dnUVZ_qudnZ2d@comcast.com... > > ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message > news:%23EJbD3HyGHA.4024@TK2MSFTNGP02.phx.gbl... > > <snip> >>> SELECT >>> t1.MyField -- never use SELECT * in production code! >>> FROM >>> dbo.Table1 t1 >>> LEFT OUTER JOIN >>> dbo.Table2 t2 >>> ON t1.MyField = t2.MyField >>> WHERE t2.MyField IS NULL; >> >> Your example works... then it breaks if I try to exclude a few addition >> records from being listed. >>> WHERE (t2.MyField IS NULL) AND (t2.AnotherField = 34) >> >> If AnotherField in Table2 is 34... then also avoid listing the matching >> records in Table1. >> >> I *DO* appreciate everyone's help with this. You are a lifesaver! > > If you want to do that, then use: > > SELECT > t1.MyField -- never use SELECT * in production code! > FROM > dbo.Table1 t1 > LEFT OUTER JOIN > dbo.Table2 t2 > ON t1.MyField = t2.MyField > AND t2.AnotherFIeld = 34 > WHERE t2.MyField IS NULL; > > Tom > > <snip> > It would be better if you had told us what the primary keys of these tables
were. And gave the desired result you want from your query for the sample data you gave. But thank you very much for including your table definitions and sample data with actual CREATE TABLE and INSERT statements. It makes it much easier to test with your sample data. I'm assuming the primary key of Table1 is Table1Emp and the primary key of Table2 is Table2Emp, Table2Color. I'm assuming the result you want is the colors that Emp 4 can vote for and the rules are: Emp 4 can't vote for a color he's already voted for, so he can't vote for Blue or Green Emp 4 can't vote for a color he proposed in Table1, so he can't vote for Green (note that the data you gave seems to violate my understanding of this rule since there is already a vote for Green, so perhaps I'm confused, but that is the understanding I'm going with Emp 4 can't vote for colors that are blank, so he can't vote for the color proposed by Emp 10 Emp 4 can't vote for colors where Table1.Table1Decided is not 0, so he can't vote for Purple, Conclusion, the only color he can vote for is Red. And I'm also assuming you want Red in the result only once even though two different employees have suggested it in Table1. I wouldn't use a LEFT JOIN at all for this, but instead a subquery that checks to see if a vote exists for each color by Emp 4, something like: SELECT DISTINCT t1.Table1Color FROM Table1 t1 WHERE -- He can't vote for colors if he was the 1 that suggested it originally t1.Table1Emp<>4 AND -- He can't vote on colors that are "" t1.Table1Color<>'' AND -- He can't vote on colors where voting closed t1.Table1Decided=0 AND -- He must not have already voted for this color NOT EXISTS (SELECT 1 FROM Table2 t2 WHERE t1.Table1Color = t2.Table2Color AND t2.Table2Emp = 4) ORDER BY t1.Table1Color That gives the result: Table1Color ---------------------- Red Tom ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message Show quote news:uW7crWJyGHA.4336@TK2MSFTNGP06.phx.gbl... > Tom, > > Here's some more info: > > -- Table1 is a list of employees, colors, and whether this color can still > be voted on or not > IF(OBJECT_ID('dbo.Table1') IS NOT NULL) DROP TABLE dbo.Table1 > GO > CREATE TABLE dbo.Table1 > ( > Table1Emp Int NOT NULL DEFAULT 0, > Table1Color VarChar(22) NOT NULL DEFAULT '', > Table1Decided SmallInt NOT NULL DEFAULT 0 > ) > GO > > INSERT INTO Table1 VALUES(2 , 'Red' , 0) > INSERT INTO Table1 VALUES(4 , 'Green' , 0) > INSERT INTO Table1 VALUES(6 , 'Red' , 0) > INSERT INTO Table1 VALUES(8 , 'Blue' , 0) > INSERT INTO Table1 VALUES(10, '' , 0) > INSERT INTO Table1 VALUES(12, 'Purple', -1) > GO > > -- Table2 is a list of employees, the color they voted for, and their > comments > IF(OBJECT_ID('dbo.Table2') IS NOT NULL) DROP TABLE dbo.Table2 > GO > CREATE TABLE dbo.Table2 > ( > Table2Emp Int NOT NULL DEFAULT 0, > Table2Color VarChar(22) NOT NULL DEFAULT '', > Table2Vote VarChar(22) NOT NULL DEFAULT '' > ) > GO > > INSERT INTO Table2 VALUES(2, 'Blue' , 'Hate it' ) > INSERT INTO Table2 VALUES(2, 'Red' , 'Love it' ) > INSERT INTO Table2 VALUES(4, 'Blue' , 'Too loud') > INSERT INTO Table2 VALUES(4, 'Green', 'Too dark') > GO > > select * from Table1 > select * from Table2 > > -- Show the rows in Table1 that employee #4 can still vote on (THIS CODE > DOES NOT WORK) > SELECT *,Table1Emp,Table2Emp FROM Table1 > LEFT JOIN Table2 ON (Table1Emp = Table2Emp) > WHERE NOT ( > (Table2Emp IS NULL) OR -- His vote is NOT found in Table2 > (Table1Emp=4) OR -- He can't vote for colors if he was the 1 that > suggested it originally > (Table1Color='') OR -- He can't vote on colors that are "" > (Table1Decided=0) OR -- He can't vote on colors that have already been > decided (voting closed) > ((Table1Emp=4) AND (Table2Emp=4)) -- He can't vote on colors that he's > already voted on > ) > > > "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message > news:H-GdnfwZDeBxyXLZnZ2dnUVZ_qudnZ2d@comcast.com... >> >> ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message >> news:%23EJbD3HyGHA.4024@TK2MSFTNGP02.phx.gbl... >> >> <snip> >>>> SELECT >>>> t1.MyField -- never use SELECT * in production code! >>>> FROM >>>> dbo.Table1 t1 >>>> LEFT OUTER JOIN >>>> dbo.Table2 t2 >>>> ON t1.MyField = t2.MyField >>>> WHERE t2.MyField IS NULL; >>> >>> Your example works... then it breaks if I try to exclude a few addition >>> records from being listed. >>>> WHERE (t2.MyField IS NULL) AND (t2.AnotherField = 34) >>> >>> If AnotherField in Table2 is 34... then also avoid listing the matching >>> records in Table1. >>> >>> I *DO* appreciate everyone's help with this. You are a lifesaver! >> >> If you want to do that, then use: >> >> SELECT >> t1.MyField -- never use SELECT * in production code! >> FROM >> dbo.Table1 t1 >> LEFT OUTER JOIN >> dbo.Table2 t2 >> ON t1.MyField = t2.MyField >> AND t2.AnotherFIeld = 34 >> WHERE t2.MyField IS NULL; >> >> Tom >> >> <snip> >> > > Tom,
Excellent. Yes... you are right on all counts. My "sample" had some typos/errors in it. Table1Emp and Table2Emp are the joining keys. I apparently didn't need a "join" at all. (I was VERY surprised by that. I thought that was the whole point of having linked tables!) Your code works! (I owe you 1, buddy.) Thanks again. Show quote "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message news:jMWdnbrEGcY6C3LZnZ2dnUVZ_vydnZ2d@comcast.com... > It would be better if you had told us what the primary keys of these > tables were. And gave the desired result you want from your query for the > sample data you gave. But thank you very much for including your table > definitions and sample data with actual CREATE TABLE and INSERT > statements. It makes it much easier to test with your sample data. > > I'm assuming the primary key of Table1 is Table1Emp and the primary key of > Table2 is Table2Emp, Table2Color. > I'm assuming the result you want is the colors that Emp 4 can vote for and > the rules are: > Emp 4 can't vote for a color he's already voted for, so he can't vote for > Blue or Green > Emp 4 can't vote for a color he proposed in Table1, so he can't vote for > Green (note that the data you gave seems to violate my understanding of > this rule since there is already a vote for Green, so perhaps I'm > confused, but that is the understanding I'm going with > Emp 4 can't vote for colors that are blank, so he can't vote for the color > proposed by Emp 10 > Emp 4 can't vote for colors where Table1.Table1Decided is not 0, so he > can't vote for Purple, > > Conclusion, the only color he can vote for is Red. > And I'm also assuming you want Red in the result only once even though two > different employees have suggested it in Table1. > > I wouldn't use a LEFT JOIN at all for this, but instead a subquery that > checks to see if a vote exists for each color by Emp 4, something like: > > SELECT DISTINCT t1.Table1Color FROM Table1 t1 > WHERE > -- He can't vote for colors if he was the 1 that suggested it originally > t1.Table1Emp<>4 AND > -- He can't vote on colors that are "" > t1.Table1Color<>'' AND > -- He can't vote on colors where voting closed > t1.Table1Decided=0 AND > -- He must not have already voted for this color > NOT EXISTS > (SELECT 1 FROM Table2 t2 > WHERE t1.Table1Color = t2.Table2Color AND > t2.Table2Emp = 4) > ORDER BY t1.Table1Color > > That gives the result: > > Table1Color > ---------------------- > Red > > Tom > > ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message > news:uW7crWJyGHA.4336@TK2MSFTNGP06.phx.gbl... >> Tom, >> >> Here's some more info: >> >> -- Table1 is a list of employees, colors, and whether this color can >> still be voted on or not >> IF(OBJECT_ID('dbo.Table1') IS NOT NULL) DROP TABLE dbo.Table1 >> GO >> CREATE TABLE dbo.Table1 >> ( >> Table1Emp Int NOT NULL DEFAULT 0, >> Table1Color VarChar(22) NOT NULL DEFAULT '', >> Table1Decided SmallInt NOT NULL DEFAULT 0 >> ) >> GO >> >> INSERT INTO Table1 VALUES(2 , 'Red' , 0) >> INSERT INTO Table1 VALUES(4 , 'Green' , 0) >> INSERT INTO Table1 VALUES(6 , 'Red' , 0) >> INSERT INTO Table1 VALUES(8 , 'Blue' , 0) >> INSERT INTO Table1 VALUES(10, '' , 0) >> INSERT INTO Table1 VALUES(12, 'Purple', -1) >> GO >> >> -- Table2 is a list of employees, the color they voted for, and their >> comments >> IF(OBJECT_ID('dbo.Table2') IS NOT NULL) DROP TABLE dbo.Table2 >> GO >> CREATE TABLE dbo.Table2 >> ( >> Table2Emp Int NOT NULL DEFAULT 0, >> Table2Color VarChar(22) NOT NULL DEFAULT '', >> Table2Vote VarChar(22) NOT NULL DEFAULT '' >> ) >> GO >> >> INSERT INTO Table2 VALUES(2, 'Blue' , 'Hate it' ) >> INSERT INTO Table2 VALUES(2, 'Red' , 'Love it' ) >> INSERT INTO Table2 VALUES(4, 'Blue' , 'Too loud') >> INSERT INTO Table2 VALUES(4, 'Green', 'Too dark') >> GO >> >> select * from Table1 >> select * from Table2 >> >> -- Show the rows in Table1 that employee #4 can still vote on (THIS CODE >> DOES NOT WORK) >> SELECT *,Table1Emp,Table2Emp FROM Table1 >> LEFT JOIN Table2 ON (Table1Emp = Table2Emp) >> WHERE NOT ( >> (Table2Emp IS NULL) OR -- His vote is NOT found in Table2 >> (Table1Emp=4) OR -- He can't vote for colors if he was the 1 that >> suggested it originally >> (Table1Color='') OR -- He can't vote on colors that are "" >> (Table1Decided=0) OR -- He can't vote on colors that have already been >> decided (voting closed) >> ((Table1Emp=4) AND (Table2Emp=4)) -- He can't vote on colors that he's >> already voted on >> ) >> >> >> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message >> news:H-GdnfwZDeBxyXLZnZ2dnUVZ_qudnZ2d@comcast.com... >>> >>> ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message >>> news:%23EJbD3HyGHA.4024@TK2MSFTNGP02.phx.gbl... >>> >>> <snip> >>>>> SELECT >>>>> t1.MyField -- never use SELECT * in production code! >>>>> FROM >>>>> dbo.Table1 t1 >>>>> LEFT OUTER JOIN >>>>> dbo.Table2 t2 >>>>> ON t1.MyField = t2.MyField >>>>> WHERE t2.MyField IS NULL; >>>> >>>> Your example works... then it breaks if I try to exclude a few addition >>>> records from being listed. >>>>> WHERE (t2.MyField IS NULL) AND (t2.AnotherField = 34) >>>> >>>> If AnotherField in Table2 is 34... then also avoid listing the matching >>>> records in Table1. >>>> >>>> I *DO* appreciate everyone's help with this. You are a lifesaver! >>> >>> If you want to do that, then use: >>> >>> SELECT >>> t1.MyField -- never use SELECT * in production code! >>> FROM >>> dbo.Table1 t1 >>> LEFT OUTER JOIN >>> dbo.Table2 t2 >>> ON t1.MyField = t2.MyField >>> AND t2.AnotherFIeld = 34 >>> WHERE t2.MyField IS NULL; >>> >>> Tom >>> >>> <snip> >>> >> >> > > ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message
Show quote news:OPYuPLTyGHA.4176@TK2MSFTNGP06.phx.gbl... > Tom, > > Excellent. > > Yes... you are right on all counts. > > My "sample" had some typos/errors in it. > > Table1Emp and Table2Emp are the joining keys. > > I apparently didn't need a "join" at all. (I was VERY surprised by that. > I thought that was the whole point of having linked tables!) > > Your code works! > > (I owe you 1, buddy.) > > Thanks again. > > > > "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message > news:jMWdnbrEGcY6C3LZnZ2dnUVZ_vydnZ2d@comcast.com... >> It would be better if you had told us what the primary keys of these >> tables were. And gave the desired result you want from your query for >> the sample data you gave. But thank you very much for including your >> table definitions and sample data with actual CREATE TABLE and INSERT >> statements. It makes it much easier to test with your sample data. >> >> I'm assuming the primary key of Table1 is Table1Emp and the primary key >> of Table2 is Table2Emp, Table2Color. >> I'm assuming the result you want is the colors that Emp 4 can vote for >> and the rules are: >> Emp 4 can't vote for a color he's already voted for, so he can't vote for >> Blue or Green >> Emp 4 can't vote for a color he proposed in Table1, so he can't vote for >> Green (note that the data you gave seems to violate my understanding of >> this rule since there is already a vote for Green, so perhaps I'm >> confused, but that is the understanding I'm going with >> Emp 4 can't vote for colors that are blank, so he can't vote for the >> color proposed by Emp 10 >> Emp 4 can't vote for colors where Table1.Table1Decided is not 0, so he >> can't vote for Purple, >> >> Conclusion, the only color he can vote for is Red. >> And I'm also assuming you want Red in the result only once even though >> two different employees have suggested it in Table1. >> >> I wouldn't use a LEFT JOIN at all for this, but instead a subquery that >> checks to see if a vote exists for each color by Emp 4, something like: >> >> SELECT DISTINCT t1.Table1Color FROM Table1 t1 >> WHERE >> -- He can't vote for colors if he was the 1 that suggested it originally >> t1.Table1Emp<>4 AND >> -- He can't vote on colors that are "" >> t1.Table1Color<>'' AND >> -- He can't vote on colors where voting closed >> t1.Table1Decided=0 AND >> -- He must not have already voted for this color >> NOT EXISTS >> (SELECT 1 FROM Table2 t2 >> WHERE t1.Table1Color = t2.Table2Color AND >> t2.Table2Emp = 4) >> ORDER BY t1.Table1Color >> >> That gives the result: >> >> Table1Color >> ---------------------- >> Red >> >> Tom >> >> ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message >> news:uW7crWJyGHA.4336@TK2MSFTNGP06.phx.gbl... >>> Tom, >>> >>> Here's some more info: >>> >>> -- Table1 is a list of employees, colors, and whether this color can >>> still be voted on or not >>> IF(OBJECT_ID('dbo.Table1') IS NOT NULL) DROP TABLE dbo.Table1 >>> GO >>> CREATE TABLE dbo.Table1 >>> ( >>> Table1Emp Int NOT NULL DEFAULT 0, >>> Table1Color VarChar(22) NOT NULL DEFAULT '', >>> Table1Decided SmallInt NOT NULL DEFAULT 0 >>> ) >>> GO >>> >>> INSERT INTO Table1 VALUES(2 , 'Red' , 0) >>> INSERT INTO Table1 VALUES(4 , 'Green' , 0) >>> INSERT INTO Table1 VALUES(6 , 'Red' , 0) >>> INSERT INTO Table1 VALUES(8 , 'Blue' , 0) >>> INSERT INTO Table1 VALUES(10, '' , 0) >>> INSERT INTO Table1 VALUES(12, 'Purple', -1) >>> GO >>> >>> -- Table2 is a list of employees, the color they voted for, and their >>> comments >>> IF(OBJECT_ID('dbo.Table2') IS NOT NULL) DROP TABLE dbo.Table2 >>> GO >>> CREATE TABLE dbo.Table2 >>> ( >>> Table2Emp Int NOT NULL DEFAULT 0, >>> Table2Color VarChar(22) NOT NULL DEFAULT '', >>> Table2Vote VarChar(22) NOT NULL DEFAULT '' >>> ) >>> GO >>> >>> INSERT INTO Table2 VALUES(2, 'Blue' , 'Hate it' ) >>> INSERT INTO Table2 VALUES(2, 'Red' , 'Love it' ) >>> INSERT INTO Table2 VALUES(4, 'Blue' , 'Too loud') >>> INSERT INTO Table2 VALUES(4, 'Green', 'Too dark') >>> GO >>> >>> select * from Table1 >>> select * from Table2 >>> >>> -- Show the rows in Table1 that employee #4 can still vote on (THIS CODE >>> DOES NOT WORK) >>> SELECT *,Table1Emp,Table2Emp FROM Table1 >>> LEFT JOIN Table2 ON (Table1Emp = Table2Emp) >>> WHERE NOT ( >>> (Table2Emp IS NULL) OR -- His vote is NOT found in Table2 >>> (Table1Emp=4) OR -- He can't vote for colors if he was the 1 that >>> suggested it originally >>> (Table1Color='') OR -- He can't vote on colors that are "" >>> (Table1Decided=0) OR -- He can't vote on colors that have already >>> been decided (voting closed) >>> ((Table1Emp=4) AND (Table2Emp=4)) -- He can't vote on colors that he's >>> already voted on >>> ) >>> >>> >>> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message >>> news:H-GdnfwZDeBxyXLZnZ2dnUVZ_qudnZ2d@comcast.com... >>>> >>>> ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message >>>> news:%23EJbD3HyGHA.4024@TK2MSFTNGP02.phx.gbl... >>>> >>>> <snip> >>>>>> SELECT >>>>>> t1.MyField -- never use SELECT * in production code! >>>>>> FROM >>>>>> dbo.Table1 t1 >>>>>> LEFT OUTER JOIN >>>>>> dbo.Table2 t2 >>>>>> ON t1.MyField = t2.MyField >>>>>> WHERE t2.MyField IS NULL; >>>>> >>>>> Your example works... then it breaks if I try to exclude a few >>>>> addition records from being listed. >>>>>> WHERE (t2.MyField IS NULL) AND (t2.AnotherField = 34) >>>>> >>>>> If AnotherField in Table2 is 34... then also avoid listing the >>>>> matching records in Table1. >>>>> >>>>> I *DO* appreciate everyone's help with this. You are a lifesaver! >>>> >>>> If you want to do that, then use: >>>> >>>> SELECT >>>> t1.MyField -- never use SELECT * in production code! >>>> FROM >>>> dbo.Table1 t1 >>>> LEFT OUTER JOIN >>>> dbo.Table2 t2 >>>> ON t1.MyField = t2.MyField >>>> AND t2.AnotherFIeld = 34 >>>> WHERE t2.MyField IS NULL; >>>> >>>> Tom >>>> >>>> <snip> >>>> >>> >>> >> >> > > Sorry, I clicked the wrong button and sent a blank message.
But I just wanted to say that I'm glad I could be of help and you don't owe me one. For every answer I given on this newsgroup, I've recieved 10 in return, either answers to my own questions or reading answers to other people's questions or reading corrections or improvements on answers I have provided. If you arre interested in SQL Server, come back to this newsgroup often, reading other people's questions and answers will help you with both avoiding "reinventing the wheel" and avoiding problems before they happen to you. It won't be long before you will be reading questions and realize "I know the answer to that" and providing answers yourself. Good luck, Tom Show quote "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message news:T-Odnbys6IyqA23ZnZ2dnUVZ_tydnZ2d@comcast.com... > > ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message > news:OPYuPLTyGHA.4176@TK2MSFTNGP06.phx.gbl... >> Tom, >> >> Excellent. >> >> Yes... you are right on all counts. >> >> My "sample" had some typos/errors in it. >> >> Table1Emp and Table2Emp are the joining keys. >> >> I apparently didn't need a "join" at all. (I was VERY surprised by >> that. I thought that was the whole point of having linked tables!) >> >> Your code works! >> >> (I owe you 1, buddy.) >> >> Thanks again. >> >> >> >> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message >> news:jMWdnbrEGcY6C3LZnZ2dnUVZ_vydnZ2d@comcast.com... >>> It would be better if you had told us what the primary keys of these >>> tables were. And gave the desired result you want from your query for >>> the sample data you gave. But thank you very much for including your >>> table definitions and sample data with actual CREATE TABLE and INSERT >>> statements. It makes it much easier to test with your sample data. >>> >>> I'm assuming the primary key of Table1 is Table1Emp and the primary key >>> of Table2 is Table2Emp, Table2Color. >>> I'm assuming the result you want is the colors that Emp 4 can vote for >>> and the rules are: >>> Emp 4 can't vote for a color he's already voted for, so he can't vote >>> for Blue or Green >>> Emp 4 can't vote for a color he proposed in Table1, so he can't vote for >>> Green (note that the data you gave seems to violate my understanding of >>> this rule since there is already a vote for Green, so perhaps I'm >>> confused, but that is the understanding I'm going with >>> Emp 4 can't vote for colors that are blank, so he can't vote for the >>> color proposed by Emp 10 >>> Emp 4 can't vote for colors where Table1.Table1Decided is not 0, so he >>> can't vote for Purple, >>> >>> Conclusion, the only color he can vote for is Red. >>> And I'm also assuming you want Red in the result only once even though >>> two different employees have suggested it in Table1. >>> >>> I wouldn't use a LEFT JOIN at all for this, but instead a subquery that >>> checks to see if a vote exists for each color by Emp 4, something like: >>> >>> SELECT DISTINCT t1.Table1Color FROM Table1 t1 >>> WHERE >>> -- He can't vote for colors if he was the 1 that suggested it originally >>> t1.Table1Emp<>4 AND >>> -- He can't vote on colors that are "" >>> t1.Table1Color<>'' AND >>> -- He can't vote on colors where voting closed >>> t1.Table1Decided=0 AND >>> -- He must not have already voted for this color >>> NOT EXISTS >>> (SELECT 1 FROM Table2 t2 >>> WHERE t1.Table1Color = t2.Table2Color AND >>> t2.Table2Emp = 4) >>> ORDER BY t1.Table1Color >>> >>> That gives the result: >>> >>> Table1Color >>> ---------------------- >>> Red >>> >>> Tom >>> >>> ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message >>> news:uW7crWJyGHA.4336@TK2MSFTNGP06.phx.gbl... >>>> Tom, >>>> >>>> Here's some more info: >>>> >>>> -- Table1 is a list of employees, colors, and whether this color can >>>> still be voted on or not >>>> IF(OBJECT_ID('dbo.Table1') IS NOT NULL) DROP TABLE dbo.Table1 >>>> GO >>>> CREATE TABLE dbo.Table1 >>>> ( >>>> Table1Emp Int NOT NULL DEFAULT 0, >>>> Table1Color VarChar(22) NOT NULL DEFAULT '', >>>> Table1Decided SmallInt NOT NULL DEFAULT 0 >>>> ) >>>> GO >>>> >>>> INSERT INTO Table1 VALUES(2 , 'Red' , 0) >>>> INSERT INTO Table1 VALUES(4 , 'Green' , 0) >>>> INSERT INTO Table1 VALUES(6 , 'Red' , 0) >>>> INSERT INTO Table1 VALUES(8 , 'Blue' , 0) >>>> INSERT INTO Table1 VALUES(10, '' , 0) >>>> INSERT INTO Table1 VALUES(12, 'Purple', -1) >>>> GO >>>> >>>> -- Table2 is a list of employees, the color they voted for, and their >>>> comments >>>> IF(OBJECT_ID('dbo.Table2') IS NOT NULL) DROP TABLE dbo.Table2 >>>> GO >>>> CREATE TABLE dbo.Table2 >>>> ( >>>> Table2Emp Int NOT NULL DEFAULT 0, >>>> Table2Color VarChar(22) NOT NULL DEFAULT '', >>>> Table2Vote VarChar(22) NOT NULL DEFAULT '' >>>> ) >>>> GO >>>> >>>> INSERT INTO Table2 VALUES(2, 'Blue' , 'Hate it' ) >>>> INSERT INTO Table2 VALUES(2, 'Red' , 'Love it' ) >>>> INSERT INTO Table2 VALUES(4, 'Blue' , 'Too loud') >>>> INSERT INTO Table2 VALUES(4, 'Green', 'Too dark') >>>> GO >>>> >>>> select * from Table1 >>>> select * from Table2 >>>> >>>> -- Show the rows in Table1 that employee #4 can still vote on (THIS >>>> CODE DOES NOT WORK) >>>> SELECT *,Table1Emp,Table2Emp FROM Table1 >>>> LEFT JOIN Table2 ON (Table1Emp = Table2Emp) >>>> WHERE NOT ( >>>> (Table2Emp IS NULL) OR -- His vote is NOT found in Table2 >>>> (Table1Emp=4) OR -- He can't vote for colors if he was the 1 that >>>> suggested it originally >>>> (Table1Color='') OR -- He can't vote on colors that are "" >>>> (Table1Decided=0) OR -- He can't vote on colors that have already >>>> been decided (voting closed) >>>> ((Table1Emp=4) AND (Table2Emp=4)) -- He can't vote on colors that he's >>>> already voted on >>>> ) >>>> >>>> >>>> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message >>>> news:H-GdnfwZDeBxyXLZnZ2dnUVZ_qudnZ2d@comcast.com... >>>>> >>>>> ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message >>>>> news:%23EJbD3HyGHA.4024@TK2MSFTNGP02.phx.gbl... >>>>> >>>>> <snip> >>>>>>> SELECT >>>>>>> t1.MyField -- never use SELECT * in production code! >>>>>>> FROM >>>>>>> dbo.Table1 t1 >>>>>>> LEFT OUTER JOIN >>>>>>> dbo.Table2 t2 >>>>>>> ON t1.MyField = t2.MyField >>>>>>> WHERE t2.MyField IS NULL; >>>>>> >>>>>> Your example works... then it breaks if I try to exclude a few >>>>>> addition records from being listed. >>>>>>> WHERE (t2.MyField IS NULL) AND (t2.AnotherField = 34) >>>>>> >>>>>> If AnotherField in Table2 is 34... then also avoid listing the >>>>>> matching records in Table1. >>>>>> >>>>>> I *DO* appreciate everyone's help with this. You are a lifesaver! >>>>> >>>>> If you want to do that, then use: >>>>> >>>>> SELECT >>>>> t1.MyField -- never use SELECT * in production code! >>>>> FROM >>>>> dbo.Table1 t1 >>>>> LEFT OUTER JOIN >>>>> dbo.Table2 t2 >>>>> ON t1.MyField = t2.MyField >>>>> AND t2.AnotherFIeld = 34 >>>>> WHERE t2.MyField IS NULL; >>>>> >>>>> Tom >>>>> >>>>> <snip> >>>>> >>>> >>>> >>> >>> >> >> > > Agree with Aaron. You can tell the difference between Customer.CustomerID
and Orders.CustomerID by qualifying them. Ditto for the other 102 columns (if you have two tables with 102 identical columns, you might consider combining them into one table). If all that extra typing of table names gets on your nerves (it does get on my nerves), alias your tables and use the alias to qualify the columns in queries. ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message Show quote news:%23EJbD3HyGHA.4024@TK2MSFTNGP02.phx.gbl... >I would start all the field-names in the customer-table with the name >"Cust" ... and all the fields in the Order table with "Ord". > So the fields would be called: CustUserId and OrdUserId > > I would think this would help *AVOID* all confusion between the 2. (Fully > qualified or not... I instantly know which table the fields are in... and > what they refer to... and the > similar names tell me they joined keys.) None of which can be done if you > call 2 (or 102) different fields... all by the same name. > > Imagine 102 fields all with identical names. It's interesting how "1 > person's confusing nightmare"... is another person's "I do that to keep > things clear". > > More...... > >> SELECT >> t1.MyField -- never use SELECT * in production code! >> FROM >> dbo.Table1 t1 >> LEFT OUTER JOIN >> dbo.Table2 t2 >> ON t1.MyField = t2.MyField >> WHERE t2.MyField IS NULL; > > Your example works... then it breaks if I try to exclude a few addition > records from being listed. >> WHERE (t2.MyField IS NULL) AND (t2.AnotherField = 34) > > If AnotherField in Table2 is 34... then also avoid listing the matching > records in Table1. > > I *DO* appreciate everyone's help with this. You are a lifesaver! > > > > > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:%23OzQuGHyGHA.4240@TK2MSFTNGP03.phx.gbl... >>> I should have said... No table I have EVER made has EVER had identical >>> field names. >> >> Are you serious? >> >> If you have a table called customers: >> >> CREATE TABLE dbo.Customers >> ( >> CustomerID ... >> >> Then you have a table called Orders, which contains a foreign key >> reference: >> >> CREATE TABLE dbo.Orders >> ( >> ..., >> CustomerID ... FOREIGN KEY REFERENCES dbo.Customers(CustomerID)... >> >> What else would you call that CustomerID column? Please don't tell me >> you would call Customers.ID instead of Customers.CustomerID. They >> represent the same entity, and should not have different names. >> >> If you have a table of States that drive a picklist so you don't have >> people typing in Gorja and Kantukkee, presumably you would have a table >> called States, with a StateID column and a StateName column, perhaps. So >> if you put StateID in multiple other tables (such as customer's >> demographics, order shipment address, manufacturer's routes, etc), you >> would make up a different name for the column StateID for each table? >> >>> Maybe I need to start qualifing *ALL* my fields when joining tables... >>> even though they only appear in 1 of the 2 tables. >> >> I always do. Using aliases cuts down on carpal. e.g. >> >> SELECT >> t1.MyField -- never use SELECT * in production code! >> FROM >> dbo.Table1 t1 >> LEFT OUTER JOIN >> dbo.Table2 t2 >> ON t1.MyField = t2.MyField >> WHERE t2.MyField IS NULL; >> >> A >> > > Yes, qualifying all references to columns is a good practice. (Although, I
will admit that when I write a query that has no joins at all, I often don't, but I approve of anyone who does.) But if I have more than one table in the query I believe you should qualify all columns. If nothing else, it allows anyone who is looking at your query and isn't completely familiar with your database to know where each column came from. Also, you should use an alias for each table in your query. It makes it easier to read and saves you from typing the whoe table name over and over again. Finding rows in one table which don't have matches in another table is easy. Suppose you have a Customers table declared as Create Table Customers (CustomerID int Primary Key, State char(2) Not Null) and an Orders table declared as Create Table Orders (OrderID int Primary Key, CustomerID int Not Null, OrderDate datetime Not Null) with, of course, a foreign key between the tables on CustomerID. BTW, many database designers will do what I did above, call the same attribute the same name in every table where it exists. I think it's less confusing than calling one CustomerID and one something like OrderCustomerID. But to each his own, just make sure your site has a standard on this and that the standard is followed by everybody. So, now the query to find all customers who have no orders would be: Select c.CustomerID From Customers c Left Join Orders o On c.CustomerID = o.CustomerID Where o.CustomerID Is Null (The reason I use o.OrderID Is Null is OrderID is in the primary key of the Orders table, so I know that if this was a real row from Orders, OrderID can't be Null. So, if it is Null, this is a row from Customers which had no match in Orders.) And, to go back to a form of your original question, why would you put additional tests in the On clause and why would you put it in the Where clause, that depends on what kind of result you are looking for (and, almost always, which table the additional test is to be done on). For example, to find all customers who have no orders since Jan 1, 2006, you could use: Select c.CustomerID From Customers c Left Join Orders o On c.CustomerID = o.CustomerID And o.OrderDate >= '20060101' Where o.OrderID Is Null But to find customers from New York who have no orders, you would use Select c.CustomerID From Customers c Left Join Orders o On c.CustomerID = o.CustomerID Where o.OrderID Is Null And c.State = 'NY' Finally, to find customers from New York who had no orders since Jan 1, 2006, you could use: Select c.CustomerID From Customers c Left Join Orders o On c.CustomerID = o.CustomerID And o.OrderDate >= '20060101' Where o.OrderID Is Null And c.State = 'NY' Tom ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message Show quote news:uW%23eOzGyGHA.4204@TK2MSFTNGP04.phx.gbl... >> The problem I have is that, because you didn't bother qualifying what >> table OtherField is in, I have at least two different answers. > > I should have said... No table I have EVER made has EVER had identical > field names. > (Not sure why anyone would do that... and make this even MORE confusing.) > > But I think you've found the problem. It *DOES* matter which table > OtherField is in... even though it's only in 1 of the tables (and doesn't > really need to be qualified.) > > Maybe I need to start qualifing *ALL* my fields when joining tables... > even though they only appear in 1 of the 2 tables. > > More.... > > What I was trying to ultimately do was to list *ALL* the rows in Table1... > only EXCLUDING the ones that have a matching field in Table2. > > List all the rows in Table1 ... EXCEPT where Table1.MyField=Table2.MyField > > I was thinking it would be as easy as changing this... but it's not: > >> SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1.MyField = >> Table2.MyField) SELECT * FROM Table1 LEFT JOIN Table2 ON >> (Table1.MyField <> Table2.MyField) > > If Table1 contains values like: > 2 > 4 > 6 > 8 > 10 > > And Table2 contains values like: > 1 > 4 > 6 > > The output should be: > 2 > 8 > 10 > > (All rows in Table1 are listed... except the matching rows in Table2) > > I want to thank everyone for the fast replies! > > > > > >> The problem I have is that, because you didn't bother qualifying what >> table OtherField is in, I have at least two different answers. > > >> ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message >> news:%23CQiEzFyGHA.1252@TK2MSFTNGP04.phx.gbl... >>> >>> Ok... I give up... how do these 2 queries differ? >>> >>> SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = >>> Table2Field) WHERE (OtherField<>6) >>> SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = >>> Table2Field) AND (OtherField<>6) >>> >>> (The "WHERE" is replaced with "AND") >>> >>> Thanks >>> >>> >> >> > > > Also, you should use an alias for each table in your query. It makes it I think my mind works a very odd way. To me "alias saves typing" but that's > easier to read and saves you from typing the whoe table name over and over > again. all.... It actually makes it HARDER for me to remember what's what. Instead of just using the 5 table names... I now have to remember I've "aliased" them into 5 *MORE* names. (A total of 10) Since I only type this query once (and put it in my code) there's really not much point in "saving a little typing". The reason I gave all my tables their original "clear, easier to use, easy to remember names" was because I *WANT* to use them in my code. (Not alias them into t1 and t2 or o or c references that have no meaning to me.... or make myself remember MORE table names.) This might shed more light on what I'm trying to do: The "Uniforms table" contains a list of proposed colors for new uniforms and which employees suggested the colors. (Some of the colors have already been decided and can no longer be voted on.) Emp=17 Color=Red Decided=False Emp=4 Color= Blue Decided=False Emp=7 Color=Green Decided=True Emp=5 Color=Orange Decided=False Emp=88 Color="" Decided=False Each employee gives their thoughts on a color and they are stored in a separate "Votes Table": Emp=2 Color=Red Votes=Too bright Emp=5 Color=Red Votes=Too dark Emp=7 Color=Red Votes=Too bright Emp=5 Color=Green Votes=Too loud I need to show each employee all the colors available in the "Uniforms table"... but EXCLUDE the colors where: This employee has already voted on it. (His record is in the Votes Table.) He suggested the color himself. (As listed in the Uniforms Table.) Don't list colors that have already been decided. (As listed in the Uniforms Table.) Don't list colors that are "". (As listed in the Uniforms Table.) (After a color has been "decided" (in the Uniforms Table)... all the matching votes in the Votes Table really aren't needed any longer.) I guess what I'm asking is.... Doing a table-join is pretty simple. But then what if you have a long list of additional qualifiers... which include (or exclude) certain records based on all kinds of additional criteria. Or... should this whole mess been designed differently???? Thank again! Show quote > Finding rows in one table which don't have matches in another table is > easy. > > Suppose you have a Customers table declared as > Create Table Customers (CustomerID int Primary Key, State char(2) Not > Null) > and an Orders table declared as > Create Table Orders (OrderID int Primary Key, CustomerID int Not Null, > OrderDate datetime Not Null) > with, of course, a foreign key between the tables on CustomerID. > > BTW, many database designers will do what I did above, call the same > attribute the same name in every table where it exists. I think it's less > confusing than calling one CustomerID and one something like > OrderCustomerID. But to each his own, just make sure your site has a > standard on this and that the standard is followed by everybody. > > So, now the query to find all customers who have no orders would be: > > Select c.CustomerID > From Customers c > Left Join Orders o On c.CustomerID = o.CustomerID > Where o.CustomerID Is Null > > (The reason I use o.OrderID Is Null is OrderID is in the primary key of > the Orders table, so I know that if this was a real row from Orders, > OrderID can't be Null. So, if it is Null, this is a row from Customers > which had no match in Orders.) > > And, to go back to a form of your original question, why would you put > additional tests in the On clause and why would you put it in the Where > clause, that depends on what kind of result you are looking for (and, > almost always, which table the additional test is to be done on). > > For example, to find all customers who have no orders since Jan 1, 2006, > you could use: > > Select c.CustomerID > From Customers c > Left Join Orders o On c.CustomerID = o.CustomerID > And o.OrderDate >= '20060101' > Where o.OrderID Is Null > > But to find customers from New York who have no orders, you would use > > Select c.CustomerID > From Customers c > Left Join Orders o On c.CustomerID = o.CustomerID > Where o.OrderID Is Null > And c.State = 'NY' > > Finally, to find customers from New York who had no orders since Jan 1, > 2006, you could use: > > Select c.CustomerID > From Customers c > Left Join Orders o On c.CustomerID = o.CustomerID > And o.OrderDate >= '20060101' > Where o.OrderID Is Null > And c.State = 'NY' > > Tom > > ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message > news:uW%23eOzGyGHA.4204@TK2MSFTNGP04.phx.gbl... >>> The problem I have is that, because you didn't bother qualifying what >>> table OtherField is in, I have at least two different answers. >> >> I should have said... No table I have EVER made has EVER had identical >> field names. >> (Not sure why anyone would do that... and make this even MORE confusing.) >> >> But I think you've found the problem. It *DOES* matter which table >> OtherField is in... even though it's only in 1 of the tables (and doesn't >> really need to be qualified.) >> >> Maybe I need to start qualifing *ALL* my fields when joining tables... >> even though they only appear in 1 of the 2 tables. >> >> More.... >> >> What I was trying to ultimately do was to list *ALL* the rows in >> Table1... >> only EXCLUDING the ones that have a matching field in Table2. >> >> List all the rows in Table1 ... EXCEPT where >> Table1.MyField=Table2.MyField >> >> I was thinking it would be as easy as changing this... but it's not: >> >>> SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1.MyField = >>> Table2.MyField) SELECT * FROM Table1 LEFT JOIN Table2 ON >>> (Table1.MyField <> Table2.MyField) >> >> If Table1 contains values like: >> 2 >> 4 >> 6 >> 8 >> 10 >> >> And Table2 contains values like: >> 1 >> 4 >> 6 >> >> The output should be: >> 2 >> 8 >> 10 >> >> (All rows in Table1 are listed... except the matching rows in Table2) >> >> I want to thank everyone for the fast replies! >> >> >> >> >> >>> The problem I have is that, because you didn't bother qualifying what >>> table OtherField is in, I have at least two different answers. >> >> >>> ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message >>> news:%23CQiEzFyGHA.1252@TK2MSFTNGP04.phx.gbl... >>>> >>>> Ok... I give up... how do these 2 queries differ? >>>> >>>> SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = >>>> Table2Field) WHERE (OtherField<>6) >>>> SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = >>>> Table2Field) AND (OtherField<>6) >>>> >>>> (The "WHERE" is replaced with "AND") >>>> >>>> Thanks >>>> >>>> >>> >>> >> >> > > This shouldn't be too difficult really, some inner and outer joins, or
exists / not exists subqueries will do this for you. As for aliases, T1, T2, or a,b,c are terrible to read and understand, but using meaningful aliases that are 3 or 4 characters can help make it more readable. i.e. EMPLOYEE_JOB_DATA could be aliased as JOB. Personally, I find it much easier to read a query with short, meaningful aliases than with 15 to 20 character table names. I do prefer the table names over the T1,T1,a,b,c aliases however. Mind you I still use a,b,c for quick test code, but never for production code that someone else needs to make sense of later. Anyway, back to your question, something like this might work... declare @Emp as varchar(10) set @Emp = 7 select unif.color from Uniforms as Unif where not exists ( select 1 from Votes as Votes where Votes.emp = @emp and Votes.color = Unif.color ) and Unif.emp <> @Emp and Unif.Decided = 'False' ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message Show quote news:OyVAeIIyGHA.2220@TK2MSFTNGP02.phx.gbl... > > Also, you should use an alias for each table in your query. It makes it > > easier to read and saves you from typing the whoe table name over and over > > again. > > I think my mind works a very odd way. To me "alias saves typing" but that's > all.... It actually makes it HARDER for me to remember what's what. > Instead of just using the 5 table names... I now have to remember I've > "aliased" them into 5 *MORE* names. (A total of 10) > > Since I only type this query once (and put it in my code) there's really not > much point in "saving a little typing". > The reason I gave all my tables their original "clear, easier to use, easy > to remember names" was because I *WANT* to use them in my code. > (Not alias them into t1 and t2 or o or c references that have no meaning to > me.... or make myself remember MORE table names.) > > > > This might shed more light on what I'm trying to do: > > The "Uniforms table" contains a list of proposed colors for new uniforms and > which employees suggested the colors. > (Some of the colors have already been decided and can no longer be voted > on.) > Emp=17 Color=Red Decided=False > Emp=4 Color= Blue Decided=False > Emp=7 Color=Green Decided=True > Emp=5 Color=Orange Decided=False > Emp=88 Color="" Decided=False > > Each employee gives their thoughts on a color and they are stored in a > separate "Votes Table": > Emp=2 Color=Red Votes=Too bright > Emp=5 Color=Red Votes=Too dark > Emp=7 Color=Red Votes=Too bright > Emp=5 Color=Green Votes=Too loud > > I need to show each employee all the colors available in the "Uniforms > table"... but > EXCLUDE the colors where: > This employee has already voted on it. (His record is in the Votes > Table.) > He suggested the color himself. (As listed in the Uniforms > Table.) > Don't list colors that have already been decided. (As listed in the > Uniforms Table.) > Don't list colors that are "". (As listed in the Uniforms Table.) > > (After a color has been "decided" (in the Uniforms Table)... all the > matching votes in the Votes Table really aren't needed any longer.) > > I guess what I'm asking is.... Doing a table-join is pretty simple. But > then what if you have a long list of additional > qualifiers... which include (or exclude) certain records based on all kinds > of additional criteria. > > Or... should this whole mess been designed differently???? > > Thank again! > > > > Finding rows in one table which don't have matches in another table is > > easy. > > > > Suppose you have a Customers table declared as > > Create Table Customers (CustomerID int Primary Key, State char(2) Not > > Null) > > and an Orders table declared as > > Create Table Orders (OrderID int Primary Key, CustomerID int Not Null, > > OrderDate datetime Not Null) > > with, of course, a foreign key between the tables on CustomerID. > > > > BTW, many database designers will do what I did above, call the same > > attribute the same name in every table where it exists. I think it's less > > confusing than calling one CustomerID and one something like > > OrderCustomerID. But to each his own, just make sure your site has a > > standard on this and that the standard is followed by everybody. > > > > So, now the query to find all customers who have no orders would be: > > > > Select c.CustomerID > > From Customers c > > Left Join Orders o On c.CustomerID = o.CustomerID > > Where o.CustomerID Is Null > > > > (The reason I use o.OrderID Is Null is OrderID is in the primary key of > > the Orders table, so I know that if this was a real row from Orders, > > OrderID can't be Null. So, if it is Null, this is a row from Customers > > which had no match in Orders.) > > > > And, to go back to a form of your original question, why would you put > > additional tests in the On clause and why would you put it in the Where > > clause, that depends on what kind of result you are looking for (and, > > almost always, which table the additional test is to be done on). > > > > For example, to find all customers who have no orders since Jan 1, 2006, > > you could use: > > > > Select c.CustomerID > > From Customers c > > Left Join Orders o On c.CustomerID = o.CustomerID > > And o.OrderDate >= '20060101' > > Where o.OrderID Is Null > > > > But to find customers from New York who have no orders, you would use > > > > Select c.CustomerID > > From Customers c > > Left Join Orders o On c.CustomerID = o.CustomerID > > Where o.OrderID Is Null > > And c.State = 'NY' > > > > Finally, to find customers from New York who had no orders since Jan 1, > > 2006, you could use: > > > > Select c.CustomerID > > From Customers c > > Left Join Orders o On c.CustomerID = o.CustomerID > > And o.OrderDate >= '20060101' > > Where o.OrderID Is Null > > And c.State = 'NY' > > > > Tom > > > > ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message > > news:uW%23eOzGyGHA.4204@TK2MSFTNGP04.phx.gbl... > >>> The problem I have is that, because you didn't bother qualifying what > >>> table OtherField is in, I have at least two different answers. > >> > >> I should have said... No table I have EVER made has EVER had identical > >> field names. > >> (Not sure why anyone would do that... and make this even MORE confusing.) > >> > >> But I think you've found the problem. It *DOES* matter which table > >> OtherField is in... even though it's only in 1 of the tables (and doesn't > >> really need to be qualified.) > >> > >> Maybe I need to start qualifing *ALL* my fields when joining tables... > >> even though they only appear in 1 of the 2 tables. > >> > >> More.... > >> > >> What I was trying to ultimately do was to list *ALL* the rows in > >> Table1... > >> only EXCLUDING the ones that have a matching field in Table2. > >> > >> List all the rows in Table1 ... EXCEPT where > >> Table1.MyField=Table2.MyField > >> > >> I was thinking it would be as easy as changing this... but it's not: > >> > >>> SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1.MyField = > >>> Table2.MyField) SELECT * FROM Table1 LEFT JOIN Table2 ON > >>> (Table1.MyField <> Table2.MyField) > >> > >> If Table1 contains values like: > >> 2 > >> 4 > >> 6 > >> 8 > >> 10 > >> > >> And Table2 contains values like: > >> 1 > >> 4 > >> 6 > >> > >> The output should be: > >> 2 > >> 8 > >> 10 > >> > >> (All rows in Table1 are listed... except the matching rows in Table2) > >> > >> I want to thank everyone for the fast replies! > >> > >> > >> > >> > >> > >>> The problem I have is that, because you didn't bother qualifying what > >>> table OtherField is in, I have at least two different answers. > >> > >> > >>> ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message > >>> news:%23CQiEzFyGHA.1252@TK2MSFTNGP04.phx.gbl... > >>>> > >>>> Ok... I give up... how do these 2 queries differ? > >>>> > >>>> SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = > >>>> Table2Field) WHERE (OtherField<>6) > >>>> SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = > >>>> Table2Field) AND (OtherField<>6) > >>>> > >>>> (The "WHERE" is replaced with "AND") > >>>> > >>>> Thanks > >>>> > >>>> > >>> > >>> > >> > >> > > > > > > Jim Underwood wrote:
> This shouldn't be too difficult really, some inner and outer joins, or Right. Even though I used "t1" in my earlier reply, I stopped using tN long > exists / not exists subqueries will do this for you. > > As for aliases, T1, T2, or a,b,c are terrible to read and understand, > but using meaningful aliases that are 3 or 4 characters can help make > it more readable. i.e. EMPLOYEE_JOB_DATA could be aliased as JOB. ago in favor of meaingful aliases. -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" > I think my mind works a very odd way. To me "alias saves typing" but If you can't keep track of Customers as c and Orders as o, then I suppose > that's all.... It actually makes it HARDER for me to remember what's what. > Instead of just using the 5 table names... I now have to remember I've > "aliased" them into 5 *MORE* names. (A total of 10) your way is better for you. :-/ > Since I only type this query once (and put it in my code) there's really Maybe a good point, if your entire codebase consists of one query.> not much point in "saving a little typing". > The reason I gave all my tables their original "clear, easier to use, easy That's fine, it was just a suggestion so that you aren't staring at > to remember names" was because I *WANT* to use them in my code. > (Not alias them into t1 and t2 or o or c references that have no meaning > to me.... or make myself remember MORE table names.) BigLongTableName constantly. Nobody is saying you have to do it, just that most of us with the experience we have find it a much easier way to work. A A_Michigan_User wrote:
> Ok... I give up... how do these 2 queries differ? Please be specific if it is Table1.OtherField or Table2.OtherField.> > SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) > WHERE (OtherField<>6) > SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) > AND (OtherField<>6) > > (The "WHERE" is replaced with "AND") > > Thanks If it is actually WHERE (Table2.OtherField<>6) then the query is in fact an inner join in disguise If it is actually AND (Table1.OtherField<>6) it means "select all the rows from Table1. For those with (Table1.OtherField<>6) also display matching ones from Table2 if they exist On the other hand WHERE (Table1.OtherField<>6) means "select only the rows from Table1 for which (Table1.OtherField<>6). Also display matching ones from Table2 if they exist and so on. As a rule i try to say away from queries w/o a where clause. however,
if they product the same results now and i'd guess they do that's why you are asking the question, it may not make that much of a difference here. However, if you join other tables you are limiting the result set to-be joined to the next table. and depending on the tables, and we don't know cause not all info was provided, there you may get different results if you join table3 on table on Table1Field = Table3Field -- Show quotethanks (as always) some day i''m gona pay this forum back for all the help i''m getting kes ""A_Michigan_User"" wrote: > > Ok... I give up... how do these 2 queries differ? > > SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) > WHERE (OtherField<>6) > SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) > AND (OtherField<>6) > > (The "WHERE" is replaced with "AND") > > Thanks > > > The first query says match up every row in Table1 with all the rows in
Table2 where Table1Field = Table2Field. If a row in Table1 has no matching rows in Table2, include that row from Table1 with all the columns from Table2 set to NULL. Then, AFTER matching up the rows using the ON clause, the WHERE clause discards any row from this intermediate result where OtherField<>6. It then returns the remaining rows. The second query says match up every row in Table1 with all the rows in Table2 where Table1Field = Table2Field And OtherField<>6. If a row in Table1 has no matching rows in Table2, include that row from Table1 with all the columns from Table2 set to NULL. It then returns all of these rows. It makes a big difference which table OtherField is in (which is not clear from your question). If OtherField is in Table1 then the first query is just a LEFT JOIN where you only want rows in Table1 Where OtherField<>6. But, if OtherField is in Table2, the first query is the same as an INNER join, since any rows in the intermediate result which are from rows in Table1 which do not have a match in Table2 will have NULL in OtherField, and the where clause testing whether NULL<>6 will not return true, so the row will be discarded. If OtherField is in Table1, then the second query will match up rows from Table2 only for those rows which have OtherField<>6 and Table1Field = Table2Field. But if it can't do the match, it will still return the row from Table1, just with the columns from Table2 set to NULL. That is, you will have all the rows from Table1, including those with OtherField=6, it's just that those rows will have the columns from Table2 set to NULL. But if OtherField is in Table2, then none of the rows from Table2 with OtherField=6 will be in the result set. So, while both of these are legal syntax, in 999,999 out of 1,000,000 cases when OtherField is in Table1, you will want SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) WHERE (OtherField<>6) and when OtherField is in Table2, you will want SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) AND (OtherField<>6) If this is still confusing, try creating some tables and sample data will some rows that have matching values in Table1Field and Table2Field and some rows with values in OtherField = 6 and some rows with values in OtherField<>6. Run both queries and examine the results. Do this process both with tables wher OtherField is in Table1, then do it again where OtherField is in Table2. I know OUTER JOINS can be confusing at first, but with a little practice they will become second nature. Good luck, Tom ""A_Michigan_User"" <Michigan_RE_M***@ameritech.net> wrote in message Show quote news:%23CQiEzFyGHA.1252@TK2MSFTNGP04.phx.gbl... > > Ok... I give up... how do these 2 queries differ? > > SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) > WHERE (OtherField<>6) > SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1Field = Table2Field) > AND (OtherField<>6) > > (The "WHERE" is replaced with "AND") > > Thanks > > >> Ok... I give up... how do these 2 queries differ? << Here is how OUTER JOINs work in SQL-92. Assume you are given:Table1 Table2 a b a c ====== ====== 1 w 1 r 2 x 2 s 3 y 3 t 4 z and the outer join expression: Table1 LEFT OUTER JOIN Table2 ON Table1.a = Table2.a <== join condition AND Table2.c = 't'; <== single table condition We call Table1 the "preserved table" and Table2 the "unpreserved table" in the query. What I am going to give you is a little different, but equivalent to the ANSI/ISO standards. 1) We build the CROSS JOIN of the two tables. Scan each row in the result set. 2) If the predicate tests TRUE for that row, then you keep it. You also remove all rows derived from it from the CROSS JOIN 3) If the predicate tests FALSE or UNKNOWN for that row, then keep the columns from the preserved table, convert all the columns from the unpreserved table to NULLs and remove the duplicates. So let us execute this by hand: Let @ = passed the first predicate Let * = passed the second predicate Table1 CROSS JOIN Table2 a b a c ========================= 1 w 1 r @ 1 w 2 s 1 w 3 t * 2 x 1 r 2 x 2 s @ 2 x 3 t * 3 y 1 r 3 y 2 s 3 y 3 t @* <== the TRUE set 4 z 1 r 4 z 2 s 4 z 3 t * Table1 LEFT OUTER JOIN Table2 a b a c ========================= 3 y 3 t <= only TRUE row ----------------------- 1 w NULL NULL Sets of duplicates 1 w NULL NULL 1 w NULL NULL ----------------------- 2 x NULL NULL 2 x NULL NULL 2 x NULL NULL 3 y NULL NULL <== derived from the TRUE set - Remove 3 y NULL NULL ----------------------- 4 z NULL NULL 4 z NULL NULL 4 z NULL NULL the final results: Table1 LEFT OUTER JOIN Table2 a b a c ========================= 1 w NULL NULL 2 x NULL NULL 3 y 3 t 4 z NULL NULL The basic rule is that every row in the preserved table is represented in the results in at least one result row. There are limitations and very serious problems with the extended equality version of an outer join used in some diseased mutant products. Consider the two Chris Date tables Suppliers SupParts supno supno partno qty ========= ============== S1 S1 P1 100 S2 S1 P2 250 S3 S2 P1 100 S2 P2 250 and let's do an extended equality outer join like this: SELECT * FROM Supplier, SupParts WHERE Supplier.supno *= SupParts.supno AND qty < 200; If I do the outer first, I get: Suppliers LOJ SupParts supno supno partno qty ======================= S1 S1 P1 100 S1 S1 P2 250 S2 S2 P1 100 S2 S2 P2 250 S3 NULL NULL NULL Then I apply the (qty < 200) predicate and get Suppliers LOJ SupParts supno supno partno qty =================== S1 S1 P1 100 S2 S2 P1 100 Doing it in the opposite order Suppliers LOJ SupParts supno supno partno qty =================== S1 S1 P1 100 S2 S2 P1 100 S3 NULL NULL NULL Sybase does it one way, Oracle does it the other and Centura (nee Gupta) lets you pick which one -- the worst of both non-standard worlds! In SQL-92, you have a choice and can force the order of execution. Either do the predicates after the join ... SELECT * FROM Supplier LEFT OUTER JOIN SupParts ON Supplier.supno = SupParts.supno WHERE qty < 200; ... or do it in the joining: SELECT * FROM Supplier LEFT OUTER JOIN SupParts ON Supplier.supno = SupParts.supno AND qty < 200; Another problem is that you cannot show the same table as preserved and unpreserved in the extended equality version, but it is easy in SQL-92. For example to find the students who have taken Math 101 and might have taken Math 102: SELECT C1.student, C1.math, C2.math FROM (SELECT * FROM Courses WHERE math = 101) AS C1 LEFT OUTER JOIN (SELECT * FROM Courses WHERE math = 102) AS C2 ON C1.student = C2.student;  |
|||||||||||||||||||||||
Other interesting topics