Simple SQL Query - Or So I thought.
-
Hi all,
Bit of a noob, still lots to learn lol
Trying to do a many to many query as follows:
SELECT * FROM clients
INNER JOIN clientscategories
ON clientscategories.idclient = clients.idclient
WHERE clientscategories.idcat IN (12, 16);
............
Values 12 & 16 are just random values for test purposes. Anyway the problem I have is if a client is in more than one category e.g. 12 & 16 then the client is retrieved multiple times. How to I need to change my query to only get a client once no matter how many cats they are in.
Thanks
-
Without sample data or a table to test against it's a guess, but you probably want to use an EXISTS or an IN, not a join.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
As I said a NOOOOOB
My table layout is
Not sure how I would go about without using a JOIN ????
Thank you for your help.
-
mattmacmillan (7/2/2015)
Hi all,Bit of a noob, still lots to learn lol
Trying to do a many to many query as follows:
SELECT * FROM clients
INNER JOIN clientscategories
ON clientscategories.idclient = clients.idclient
WHERE clientscategories.idcat IN (12, 16);
............
Values 12 & 16 are just random values for test purposes. Anyway the problem I have is if a client is in more than one category e.g. 12 & 16 then the client is retrieved multiple times. How to I need to change my query to only get a client once no matter how many cats they are in.
Thanks
Welcome to the forums.
The way your query is written it will return everything in both tables. There may well be a couple of ways of solving this but it'll be much easier to give you precise advice if you post some sample data. Have a read of the link if my signature to see how.
At face value, you could try selecting only the columns you actually want to see and using SELECT DISTINCT if the rows are unique.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
mattmacmillan (7/2/2015)
My table layout isWhich doesn't help me to test queries that I write 😉
Something like
SELECT <stuff>
FROM Clients c
WHERE c.idclient IN (SELECT cc.idclient FROM clientscategories cc WHERE cc.idcat IN (12, 16));
If you want tested and working queries in the future, what we ask here is for the table definitions in a format that allows me to easily create them on my machine, ie CREATE TABLE statements and sample data that's easy to use ie INSERT statements. Especially important when the problem is complex, as showing what's wanted is often easier than explaining it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Wow some seriously fast responses here. Thanks
I will try what you have posted and if I get nowhere will post some sample data up. Thanks a lot.
-
BWFC (7/2/2015)
At face value, you could try selecting only the columns you actually want to see and using SELECT DISTINCT if the rows are unique.Would not be my first, second or third choice. Probably my last resort. Selecting too much data and then using DISTINCT to 'fix' it is generally a poor practice.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (7/2/2015)
BWFC (7/2/2015)
At face value, you could try selecting only the columns you actually want to see and using SELECT DISTINCT if the rows are unique.Would not be my first, second or third choice. Probably my last resort. Selecting too much data and then using DISTINCT to 'fix' it is generally a poor practice.
True, but it was one option, presented as something the OP could try, with nothing much to go on. It is something I always try and avoid though.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you , this did solve it.
Appreciate all responses. Cheers
GilaMonster (7/2/2015)
mattmacmillan (7/2/2015)
My table layout isWhich doesn't help me to test queries that I write 😉
Something like
SELECT <stuff>
FROM Clients c
WHERE c.idclient IN (SELECT cc.idclient FROM clientscategories cc WHERE cc.idcat IN (12, 16));
If you want tested and working queries in the future, what we ask here is for the table definitions in a format that allows me to easily create them on my machine, ie CREATE TABLE statements and sample data that's easy to use ie INSERT statements. Especially important when the problem is complex, as showing what's wanted is often easier than explaining it.
-
Do you understand why and how it works?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply