[solved] Query Help (sorry about vague subject)

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

[solved] Query Help (sorry about vague subject)

Author

Topic

mnbv0987
Starting Member

6 Posts

Posted - 2011-11-22 : 09:39:04

I have three tables, the relevant columns are:

DATASETS:
   dataset_id (pk)
   ... other data ...
   
GROUPS:
   group_id (pk)
   ... other data ...

SAMPLES:
   sample_id (pk)
   dataset_id (fk => DATASETS)
   group_id (fk => DATASETS)
   ... other data ...

Now, given a group_id, I want to select all DATASET rows that have at least one entry with the given group_id in SAMPLES. What query would do this for me?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 09:40:48

what you need is a simple join between tables on related columns. Have a look at INNER JOIN and try it yourself. If you face any issues we will help out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mnbv0987
Starting Member

6 Posts

Posted - 2011-11-22 : 09:44:14

I know how to get the dataset_ids, by the way, but I'm not sure how to get rows from DATASETs using this list.

SELECT dataset_id FROM samples WHERE group_id=? GROUP BY dataset_id

That will get me the dataset_ids themselves, but this is not quite what I want.

mnbv0987
Starting Member

6 Posts

Posted - 2011-11-22 : 09:49:46

visakh, thanks for your reply. I've tried this (I made up d.other_data):

SELECT
	s.dataset_id, d.other_data
FROM
	[SAMPLES] s, [DATASETS] d
WHERE
	group_id = ? AND s.dataset_id = d.dataset_id
GROUP BY
	s.dataset_id

But that doesn't work because d.other_data isn't in the GROUP BY. I'm having trouble figuring out where to put the join in that syntax.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 09:52:13

yu dont need group by. you just need this as per your initial explanation


SELECT
	s.dataset_id, d.*
FROM
	[SAMPLES] s

INNER JOIN [DATASETS] d
ON	d.dataset_id  = s.dataset_id 
WHERE group_id =your passed group id value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mnbv0987
Starting Member

6 Posts

Posted - 2011-11-22 : 10:01:37

Visakh, thanks! I'm sorry, it looks like I was unclear about a key bit of information in my question. There can be more than one entry in DATASETs that have the same dataset_id and group_id. It's important that I don't select duplicate SAMPLES rows in these cases. I need the SAMPLEs with at least one corresponding entry in DATASETs for a given group ID.

That said, I figured it out with subqueries:

SELECT
    *
FROM
    [DATASETS]
WHERE
    [dataset_id]
IN (
    SELECT
        [dataset_id]
    FROM
        [SAMPLES]
    WHERE
        group_id = ?
    GROUP BY
        [dataset_id]
)

Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 10:04:55

[code]SELECT
s.dataset_id, d.*
FROM
(SELECT dataset_id
FROM [SAMPLES]
WHERE group_id =your passed group id value
GROUP BY dataset_id)s
INNER JOIN [DATASETS] d
ON d.dataset_id = s.dataset_id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mnbv0987
Starting Member

6 Posts

Posted - 2011-11-22 : 10:06:19

Thanks. Would you recommend your query with a JOIN over mine with IN? Or is just six of one half dozen of the other?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 10:09:00

compare the execution times and see. I've had few occasions where JOIn worked better than IN, of course with proper indexes
anyways, worth checking in your scenario

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mnbv0987
Starting Member

6 Posts

Posted - 2011-11-22 : 10:16:19

Awesome, thank you so much for your help! (And so fast too!)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 10:46:35

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources