| Author |
Topic |
|
coatimundi
Starting Member
4 Posts |
 Posted - 2008-08-01 : 19:32:00
|
| Hi all and thanks for reading.I've come across a query conundrum.This is the configuration:There is a documents table.There is a "document associations" table that has a one-to-many relationship to the documents table.I wish to join the document associations table in a query to the documents table, but not actually select any fields from it (I just need to add it in the where clause if and when someone's searching it).There need not be a corresponding row in the document associations table, so a LEFT JOIN could produce a NULL for the row (which is why INNER JOIN) is out.There is a chance that there will be multiple results in the document associations table for an entry in the documents table.I cannot group because, in order to maintain a single result for each entry in the documents table, I would have to run an aggregate function on the document associations field.So, since I have no desire to pull any fields from this other table (and thus theoretically not affect the results set), is there a way to join that won't leave me with multiple rows and/or skewed results?Thanks in advance,-Colin |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-02 : 05:32:44
|
| Very difficult to suggest anything without we understanding what your exact scenario is. Can you explain you scenario with some sample data in which case we may be able to give you correct approach. |
 |
|
|
coatimundi
Starting Member
4 Posts |
Posted - 2008-08-02 : 14:07:39
|
Okay...With:Documents:DocID DocName1 My Doc2 My Other DocDocument Association:DocID_FK DocAssocType DocAssocID1 Department 51 Team 31 Department 1 ("Doc Assoc ID" corresponds to, in this case, the Team or Department ID)A left join will result in multiple rows being selected.SELECT d.* FROM documents d LEFT JOIN documentAssociation da ON d.DocID=DocID_FK WHERE da.DocAssocType='Department'DocID DocName1 My Doc1 My Doc2 My Other Doc An inner join will exclude rows.SELECT d.* FROM documents d INNER JOIN documentAssociation da ON d.DocID=DocID_FK WHERE da.DocAssocType='Department'DocID DocName1 My Doc What I want:DocID DocName1 My Doc2 My Other Doc Thanks again for any help... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-02 : 14:18:10
|
| then why do you want second table? what's the where condition field you want to include from second table? |
|
|
|
coatimundi
Starting Member
4 Posts |
Posted - 2008-08-02 : 15:27:43
|
quote:
Originally posted by visakh16
then why do you want second table? what's the where condition field you want to include from second table?
da.DocAssocType='Department'Could be querying the first table ("Documents"). I just didn't include it in the example.There's actually a third table involved (actually, the query is using something like 7 tables in all), because that "Department" is a foreign key.Nevermind, duh... duh... duh.SELECT DISTINCTI don't like its behavior in MS SQL and never use it, but it works well in this instance since I'm not selecting any columns from the table that would produce multiple rows.Thanks anyway... |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-08-03 : 09:30:25
|
| I agree distinct is usally wrong. If I understand correctly you can use EXISTS or IN.Your examples don't stack up though. A left join to DA where DocAssocType='Department' would effectively make it an inner join which is not what you said. |
|
|
|
coatimundi
Starting Member
4 Posts |
Posted - 2008-08-04 : 11:11:30
|
quote:
Originally posted by LoztInSpace
I agree distinct is usally wrong. If I understand correctly you can use EXISTS or IN.Your examples don't stack up though. A left join to DA where DocAssocType='Department' would effectively make it an inner join which is not what you said.
Wouldn't IN require a subquery though? I really don't want to go that route because the query is already so hefty and I actually have to use a subquery elsewhere for another table.Sorry, that was a bad example. Really, I'm looking for single rows in result set when there's essentially no WHERE condition or the WHERE is on a field in the documents table, which would require a LEFT JOIN.The whole thing got screwed over anyway. I was trying to create a view, but I would have had to select those fields from the DA table in order to query them. So, I ended up having to create the view without this little join and then join it in the query of the view with a DISTINCT clause. It seems to work.Thanks for the responses though. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-04 : 13:06:32
|
quote:
Originally posted by coatimundi
quote:
Originally posted by LoztInSpace
I agree distinct is usally wrong. If I understand correctly you can use EXISTS or IN.Your examples don't stack up though. A left join to DA where DocAssocType='Department' would effectively make it an inner join which is not what you said.
Wouldn't IN require a subquery though? I really don't want to go that route because the query is already so hefty and I actually have to use a subquery elsewhere for another table.Sorry, that was a bad example. Really, I'm looking for single rows in result set when there's essentially no WHERE condition or the WHERE is on a field in the documents table, which would require a LEFT JOIN.The whole thing got screwed over anyway. I was trying to create a view, but I would have had to select those fields from the DA table in order to query them. So, I ended up having to create the view without this little join and then join it in the query of the view with a DISTINCT clause. It seems to work.Thanks for the responses though.
if your tables are related in one to many then definitely you have to either use GROUP BY or DISTINCT if you want single row result. Another point is since you're using LEFT JOIN you need to include a condition in WHERE condition where each column from second table is evaluated to NULL just in case you want to return unmatched ones without applying the filter. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-08-05 : 09:36:24
|
quote:
Originally posted by coatimundiWouldn't IN require a subquery though? I really don't want to go that route because the query is already so hefty and I actually have to use a subquery elsewhere for another table.
Well that depends on if you want performance over correct results. You can't really exclude any technique if it is the difference between giving the correct results and not. Otherwise why not just return SELECT * FROM DOCUMENTS which will be very fast. Just hope the users don't notice  |
|
|
|
|
a join to a one-to-many without a group by