CODEWITHSUNDEEP
sqlsql-server
Phil
Phil

Reputation: 1841

Make these 2 sql statements into one with the same functionality

I have sql statement one;

select linkscat.id, linkscat.category 
from linkscat, contentlinks, links 
where contentlinks.linksid = links.id 
  and contentlinks.contentid = @contentid 
  and links.linkscatid = linkscat.id 
order by linkscat.category

and sql statement 2 takes a parameter called @linkscat which is 'id' from the statement above;

select * from links where linkscatid= @linkscat

I'm running into all types of trouble trying to use many sqldatareaders, nested repeaters etc, but it would be great if all the work could be done in the one statement? Is this possible and if so please can you help by posting the final statement?

Thanks greatly, any help much appreciated!

Upvotes: 0

Views: 88

Answers (3)

Ray
Ray

Reputation: 21905

You should be able to just add the fields you need from the links table to the query directly. I suggest you use actual field names from the links table, rather than *.

select linkscat.id, linkscat.category, links.field_1, links.field_2, ...
    from linkscat, contentlinks, links 
    where contentlinks.linksid = links.id 
      and contentlinks.contentid = @contentid 
      and links.linkscatid = linkscat.id 
    order by linkscat.category

Upvotes: 0

Oded
Oded

Reputation: 499002

You are already using joins, so you can simply add the wanted fields to the select clause:

select linkscat.id, linkscat.category, links.* 
from linkscat, contentlinks, links 
where contentlinks.linksid = links.id 
  and contentlinks.contentid = @contentid 
  and links.linkscatid = linkscat.id 
order by linkscat.category

Though I would state only the required fields instead of using *, and would use explicit joins:

select linkscat.id, linkscat.category, links.link, links.name  
from linkscat
  inner join links
   on links.linkscatid = linkscat.id 
  inner join contentlinks
   on contentlinks.linksid = links.id 
where contentlinks.contentid = @contentid 
order by linkscat.category

Upvotes: 2

MJB
MJB

Reputation: 7686

How about 

select * from links where linkscatid in (
    select linkscat.id
    from linkscat, contentlinks, links 
    where contentlinks.linksid = links.id 
      and contentlinks.contentid = @contentid 
      and links.linkscatid = linkscat.id 
    order by linkscat.category)

But I am uncertain of your table formats. Perhaps it should be select * from links where id in ... instead.

Upvotes: 0

Related Questions