sql

[jonniejoejonson]

Hey guys,

You're help would be much appreciated...

If I have the following table and sample data... myGroupTable

 

group_Id : user_Id

1 : 3

1 : 7

1 : 100

4 : 42

4 : 98

4 : 13

 

1. I would like a sql statement that would...

Return a group_Id that has exactly the specified user_Id's in them.

eg... is there a group_Id that has User_Id's 3, 7 and 100

answer: group_id 1.

 

2. If I were to have a table like the above one... would I just index group_id

 

Kind regards J

[Muddy_Funster]

 SELECT group_Id FROM table WHERE ((user_Id = #value1) AND (user_Id = #value2) AND( user_Id = #value3)) ORDER BY group_Id ASC

 

No, index any column that you will be using alot in a WHERE statement.

[fenway]

First, use IN -- WHERE user_Id IN '#value1',''#value2',''#value3')

 

Second, index choice is highly dependent on multiple usage scenarios -- in this case, user_Id would be a good choice, assuming this style represents a major percentage of the queries you're running on this table.

[jonniejoejonson]

Hey guys,

thanks for your responses, but neither is doing what I had hoped...

@fenway If I use your method it selects all rows that the the user_Id's are in... but I only want them to be selected if they are an exact match....

 

 

eg: If I have the following table and sample data... myGroupTable

Hey guys,

You're help would be much appreciated...

If I have the following table and sample data... myGroupTable

 

group_Id : user_Id

1 : 3

1 : 7

1 : 100

2 : 3

2 : 7

2 : 100

2 : 104

4 : 42

4 : 98

4 : 13

 

I would like a sql statement that would...

Return a group_Id that has exactly the specified user_Id's in them.

eg... is there a group_Id that has User_Id's 3, 7 and 100

answer: group_id 1.

Please note that I dont want it to return a group_Id 2, as that also has a user_Id of 104 in it...

Kind regards J

[fenway]

Oh -- if you want all 3 to match, then you'll need to JOIN, one for each value.

[Pandemikk]

Surely the first solution worked as needed? Even if it does have an unnecessary amount of parentheses.

 

SELECT `group_Id`
FROM `myGroupTable`
WHERE `user_Id` = 3
    AND `user_Id` = 7
    AND `user_Id` = 100
GROUP BY `group_Id`
ORDER BY `group_Id` DESC

 

Or did you just plug in what Muddy provided without actually putting in the proper variables?

[fenway]

Surely the first solution worked as needed? Even if it does have an unnecessary amount of parentheses.

 

SELECT `group_Id`
FROM `myGroupTable`
WHERE `user_Id` = 3
    AND `user_Id` = 7
    AND `user_Id` = 100
GROUP BY `group_Id`
ORDER BY `group_Id` DESC

 

Or did you just plug in what Muddy provided without actually putting in the proper variables?

This will NOT work at all.  userID can't be 3 things at once.  I didn't really Muddy suggested that -- it's a common misconception.

 

This, OTOH, will work:

SELECT t1.`group_Id`
FROM `myGroupTable` AS t1
CROSS JOIN `myGroupTable` AS t2
CROSS JOIN `myGroupTable` AS t3
WHERE t1.`user_Id` = 3
    AND t2.`user_Id` = 7
    AND t3.`user_Id` = 100

It's more efficient, too.

[jonniejoejonson]

Thanks Fenway. I appreciate your help.

[Pandemikk]

Surely the first solution worked as needed? Even if it does have an unnecessary amount of parentheses.

 

SELECT `group_Id`
FROM `myGroupTable`
WHERE `user_Id` = 3
    AND `user_Id` = 7
    AND `user_Id` = 100
GROUP BY `group_Id`
ORDER BY `group_Id` DESC

 

Or did you just plug in what Muddy provided without actually putting in the proper variables?

This will NOT work at all.  userID can't be 3 things at once.  I didn't really Muddy suggested that -- it's a common misconception.

 

This, OTOH, will work:

SELECT t1.`group_Id`
FROM `myGroupTable` AS t1
CROSS JOIN `myGroupTable` AS t2
CROSS JOIN `myGroupTable` AS t3
WHERE t1.`user_Id` = 3
    AND t2.`user_Id` = 7
    AND t3.`user_Id` = 100

It's more efficient, too.

 

I suppose it couldn't. Good solution.