SQL ?: is this possible?

  • Thread starter Thread starter neverstill
  • Start date Start date
N

neverstill

Hi-

I have to related tables that I am reading into a DataSet

[table1]
ProdId int(pk)
Name vchar

[table2]
CatId int (pk)
ProdId int (fk)
CatName vchar


I want to get all the records from table 1 that have corresponding records
in table 2. So if a record in table1 does not have any related records in
table2, I don't want to get it. Is there a way to check for this in a
SELECT command?

I can't think how.... it's hurting my head. ;)

Any help appreciated!
- Steve
 
Hi,

Well, I don't know what you want to do with these tables once they're in
datatable form, but why not select them by a join:
select b.catid, b.prodid, b.catname, t.prodid, t.name from table1 t join
table2 b on t.prodid = b.prodid

and then use the join in the dataset/datatable.

HTH,

Bernie Yaeger
 
DataTables in a DataSet do not yet allow you to perform
joins on them in memory.

You either have to do that in SQL and get one table back
in your dataset or you have to manually compare the
records from the 2 datatables to filter what you need.

HTH,
Suresh.
 
If you walk through the records of table one in a loop, you can then use the
given value in the select statment of table2 and see if you get any rows.
I"m guessing you aren't using a DataRelation b/c it would blow up with there
were child records without parents..

There may be an easier way, but you can loop through the rows and fire a
select in the second table and that should work.

HTH,

Bill
 
Wow... lot's of responses, thanks!

Of all the solutions, the one that makes the most sense is the one that
would loop through and check for related rows in the other table. I think I
will do that. Unless there is some magical way to do it better, that one
makes sense. It's an obvious one too, I just thought I would see if there
was a way to check for existence in SQL and go from there.

Thanks again all!

William Ryan said:
If you walk through the records of table one in a loop, you can then use the
given value in the select statment of table2 and see if you get any rows.
I"m guessing you aren't using a DataRelation b/c it would blow up with there
were child records without parents..

There may be an easier way, but you can loop through the rows and fire a
select in the second table and that should work.

HTH,

Bill
neverstill said:
Hi-

I have to related tables that I am reading into a DataSet

[table1]
ProdId int(pk)
Name vchar

[table2]
CatId int (pk)
ProdId int (fk)
CatName vchar


I want to get all the records from table 1 that have corresponding records
in table 2. So if a record in table1 does not have any related records in
table2, I don't want to get it. Is there a way to check for this in a
SELECT command?

I can't think how.... it's hurting my head. ;)

Any help appreciated!
- Steve
Click to expand...
Click to expand...
 
To get just fields from table1:

SELECT [columnlist] FROM table1 WHERE ProdID IN (SELECT DISTINCT ProdID FROM
Table2)

To get fields from both tables:

SELECT [columnlist] FROM table1 INNER JOIN table2 ON table1.ProdID =
table2.ProdID

--Bob

neverstill said:
Wow... lot's of responses, thanks!

Of all the solutions, the one that makes the most sense is the one that
would loop through and check for related rows in the other table. I think I
will do that. Unless there is some magical way to do it better, that one
makes sense. It's an obvious one too, I just thought I would see if there
was a way to check for existence in SQL and go from there.

Thanks again all!

William Ryan said:
If you walk through the records of table one in a loop, you can then use the
given value in the select statment of table2 and see if you get any rows.
I"m guessing you aren't using a DataRelation b/c it would blow up with there
were child records without parents..

There may be an easier way, but you can loop through the rows and fire a
select in the second table and that should work.

HTH,

Bill
neverstill said:
Hi-

I have to related tables that I am reading into a DataSet

[table1]
ProdId int(pk)
Name vchar

[table2]
CatId int (pk)
ProdId int (fk)
CatName vchar


I want to get all the records from table 1 that have corresponding records
in table 2. So if a record in table1 does not have any related
Click to expand...
Click to expand...
records
in
Click to expand...
Click to expand...
 
Sorry, did a premature send -- not even sure how ;-)

Let's try this again:

To get just fields from table1:

SELECT [columnlist] FROM table1
WHERE ProdID IN (SELECT DISTINCT ProdID FROM Table2)

To get fields from both tables:

SELECT [columnlist including aggregate functions for table2 fields] FROM
table1
INNER JOIN table2 ON table1.ProdID = table2.ProdID
GROUP BY table1.ProdID
WHERE ProdID IN (SELECT DISTINCT ProdID FROM Table2)

This is just off the top of my head -- please test in Query Analyzer by hand
first --

--Bob

neverstill said:
Wow... lot's of responses, thanks!

Of all the solutions, the one that makes the most sense is the one that
would loop through and check for related rows in the other table. I think I
will do that. Unless there is some magical way to do it better, that one
makes sense. It's an obvious one too, I just thought I would see if there
was a way to check for existence in SQL and go from there.

Thanks again all!

William Ryan said:
If you walk through the records of table one in a loop, you can then use the
given value in the select statment of table2 and see if you get any rows.
I"m guessing you aren't using a DataRelation b/c it would blow up with there
were child records without parents..

There may be an easier way, but you can loop through the rows and fire a
select in the second table and that should work.

HTH,

Bill
neverstill said:
Hi-

I have to related tables that I am reading into a DataSet

[table1]
ProdId int(pk)
Name vchar

[table2]
CatId int (pk)
ProdId int (fk)
CatName vchar


I want to get all the records from table 1 that have corresponding records
in table 2. So if a record in table1 does not have any related
Click to expand...
Click to expand...
records
in
Click to expand...
Click to expand...
 
Hi Bob,

Although I'm not sure what it happening here... I will look into it and
learn what SELECT DISTINCT.

What did you mean by "columnlist including aggregate functions for table2
fields"


Thanks for the detailed response!
-SK


Bob Grommes said:
Sorry, did a premature send -- not even sure how ;-)

Let's try this again:

To get just fields from table1:

SELECT [columnlist] FROM table1
WHERE ProdID IN (SELECT DISTINCT ProdID FROM Table2)

To get fields from both tables:

SELECT [columnlist including aggregate functions for table2 fields] FROM
table1
INNER JOIN table2 ON table1.ProdID = table2.ProdID
GROUP BY table1.ProdID
WHERE ProdID IN (SELECT DISTINCT ProdID FROM Table2)

This is just off the top of my head -- please test in Query Analyzer by hand
first --

--Bob

neverstill said:
Wow... lot's of responses, thanks!

Of all the solutions, the one that makes the most sense is the one that
would loop through and check for related rows in the other table. I
Click to expand...
think
I
will do that. Unless there is some magical way to do it better, that one
makes sense. It's an obvious one too, I just thought I would see if there
was a way to check for existence in SQL and go from there.

Thanks again all!

William Ryan said:
If you walk through the records of table one in a loop, you can then
Click to expand...
use
the
given value in the select statment of table2 and see if you get any rows.
I"m guessing you aren't using a DataRelation b/c it would blow up with there
were child records without parents..

There may be an easier way, but you can loop through the rows and fire a
select in the second table and that should work.

HTH,

Bill
Hi-

I have to related tables that I am reading into a DataSet

[table1]
ProdId int(pk)
Name vchar

[table2]
CatId int (pk)
ProdId int (fk)
CatName vchar


I want to get all the records from table 1 that have corresponding records
in table 2. So if a record in table1 does not have any related
Click to expand...
records
in
table2, I don't want to get it. Is there a way to check for this in a
SELECT command?

I can't think how.... it's hurting my head. ;)

Any help appreciated!
- Steve
Click to expand...
Click to expand...
Click to expand...
 
OK, all is good!

the WHERE IN is exactly what I wanted. Even without it, it looks like the
INNER JOIN would only return results where it could indeed join, but now
it's double safe.
Thanks for the help. SQL is pretty neat, I will need to read up much more
on this, could make my life easier.... ;)


-SK
Bob Grommes said:
Sorry, did a premature send -- not even sure how ;-)

Let's try this again:

To get just fields from table1:

SELECT [columnlist] FROM table1
WHERE ProdID IN (SELECT DISTINCT ProdID FROM Table2)

To get fields from both tables:

SELECT [columnlist including aggregate functions for table2 fields] FROM
table1
INNER JOIN table2 ON table1.ProdID = table2.ProdID
GROUP BY table1.ProdID
WHERE ProdID IN (SELECT DISTINCT ProdID FROM Table2)

This is just off the top of my head -- please test in Query Analyzer by hand
first --

--Bob

neverstill said:
Wow... lot's of responses, thanks!

Of all the solutions, the one that makes the most sense is the one that
would loop through and check for related rows in the other table. I
Click to expand...
think
I
will do that. Unless there is some magical way to do it better, that one
makes sense. It's an obvious one too, I just thought I would see if there
was a way to check for existence in SQL and go from there.

Thanks again all!

William Ryan said:
If you walk through the records of table one in a loop, you can then
Click to expand...
use
the
given value in the select statment of table2 and see if you get any rows.
I"m guessing you aren't using a DataRelation b/c it would blow up with there
were child records without parents..

There may be an easier way, but you can loop through the rows and fire a
select in the second table and that should work.

HTH,

Bill
Hi-

I have to related tables that I am reading into a DataSet

[table1]
ProdId int(pk)
Name vchar

[table2]
CatId int (pk)
ProdId int (fk)
CatName vchar


I want to get all the records from table 1 that have corresponding records
in table 2. So if a record in table1 does not have any related
Click to expand...
records
in
table2, I don't want to get it. Is there a way to check for this in a
SELECT command?

I can't think how.... it's hurting my head. ;)

Any help appreciated!
- Steve
Click to expand...
Click to expand...
Click to expand...
 
Back
Top