June 16, 2011 at 5:45 am
hi,
i m fetching data from 4 tables.i need to get the data from the table which is having unique id....but im getting all the data from that table..here is the query..can anyone help me out..
select ct.name,cot.id
from tags ct,ct_tags cot,doc_details cds,cont_poperties ccp
where ct.tagid=cot.tag_id and cot.contentid=cds.contentid and cds.contentid=ccp.contentid
i need to get it as
eg: name ---- id
test ---- id1
test1
June 16, 2011 at 6:43 am
The chances of the problem is the join you're doing is huge (135.32%).
Use the INNER join clause instead of doing everything in WHERE. This helps organize your code. Then, redo them all and try again.
By including each table in your query, run and see if there is a Cartesian product.
June 16, 2011 at 8:12 am
Or you could just add distinct to your select.
select DISTINCT ct.name,cot.id
from tags ct,ct_tags cot,doc_details cds,cont_poperties ccp
where ct.tagid=cot.tag_id and cot.contentid=cds.contentid and cds.contentid=ccp.contentid
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 16, 2011 at 8:40 am
Sean's idea of distinct is good, but you also shouldn't write joins like that. It's easy to implement a cross join somewhere.
Move to an ANSI standard, like this:
select DISTINCT
ct.name,cot.id
from tags ct
inner join ct_tags cot
on ct.tagid=cot.tag_id
inner join doc_details cds
on cot.contentid=cds.contentid
inner join cont_poperties ccp
on cds.contentid=ccp.contentid
June 16, 2011 at 8:46 am
Sean Lange (6/16/2011)
Or you could just add distinct to your select.
select DISTINCT ct.name,cot.id
from tags ct,ct_tags cot,doc_details cds,cont_poperties ccp
where ct.tagid=cot.tag_id and cot.contentid=cds.contentid and cds.contentid=ccp.contentid
Using distinct just in case you are making a casual query.
Using distinct routines in the system is far from being a good practice. If you have problems or something like join your query may take longer to process.
June 16, 2011 at 11:42 pm
hi,
thanks for the replies...i tried what Steve Jones had given...i m getting all the data but i need is to get for particular id i need the corresponding names...can any one help me out...
June 17, 2011 at 4:14 am
June 17, 2011 at 4:22 am
hi,
i used where clause..here it is my query
select DISTINCT
ct.tagname,cot.contentid
from tags ct
inner join ct_tags cot
on ct.tagid=cot.tag_id
inner join doc_details cds
on cot.contentid=cds.contentid
inner join cont_poperties ccp
on cds.contentid=ccp.contentid
where ct.tagid=cot.tag_id and cot.contentid=cds.contentid and cds.contentid=ccp.contentid
group by
ct.tagname
i m getting same output..can u provide me where my query goes wrong..
June 17, 2011 at 5:15 am
hi,
i had done the query like this SELECT ct.tagname,B.contentid
FROM tags ct
JOIN (SELECT DISTINCT tagid FROM tags ) tbl ON ct.tagid = tbl.tagid
INNER JOIN ct_tags B ON ct.tagid = B.tag_id
INNER JOIN doc_details C ON B.contentid = C.contentid
WHERE ct.tagid IN (SELECT DISTINCT tag_id from doct_details)
event though im getting same not the required one
June 17, 2011 at 9:23 am
ksmadhuri (6/16/2011)
hi,thanks for the replies...i tried what Steve Jones had given...i m getting all the data but i need is to get for particular id i need the corresponding names...can any one help me out...
This sentence doesn't make sense. We can't see the data, so we can't understand what isn't working.
If you need one particular ID, use another clause in the WHERE to limit to this.
...
and MyID = 4
If it's something else, you need to stop and explain it more clearly (take another approach to explain which data isn't coming through), or mock something up with 10-20 rows of data and some tables and post the DDL/DML
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply