February 17, 2010 at 2:23 pm
Hi,
The issue is next:
I have tables Constituents, Refs and Images.
Constituents table has both artists and owners, ConstituentTypeID is the one which differentiate them (1 and 2).
How can I query for specific (or all) row(s) from Images and Constituents tables?
Ref is connection between them (Ref.ID = Images.ID and Ref.ConstituentID = Constituents.ConstituentID)
Thanks,
Brano
February 17, 2010 at 2:30 pm
I'd love to help but I'm having a hard time understanding the problem.
Try to create a scenario for us to work with so we can understand the problem.
here are the famous posting guidelines:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Craig Outcalt
February 17, 2010 at 2:46 pm
having a hard time trying to understand what the conundrum is... could you clarify, please?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 17, 2010 at 2:51 pm
Thank you Craig, Pablo, for your quick response.
I will try.
I have to pull data from tables I mention, to looks something like this:
[Image Name] from Images table,
Artists (ConstituentTypeID=1) from Constituents table,
Donor (ConstituentTypeID=2) from Constituents table,
where Ref table is connection between them (Ref.ID = Images.ID and Ref.ConstituentID = Constituents.ConstituentID)
If this is not clear enough, please advise,
Brano
February 17, 2010 at 2:53 pm
Please refer to the posting guidelines but it sounds like you may need an OUTER JOIN and/or a UNION, etc but without providing the information as stated in the posting guidelines you will probably not get a response that meets your needs.
Also I'm not sure that your Database is in 3rd Normal Form...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 17, 2010 at 3:10 pm
Thanks SSC,
It is database connected with application.
My task is administration, sometimes reports. I am not sure about normalization, but trying not to go there :-).
I do not know how to use posting guidelines in this case, since request came in the way I described.
Brano
February 17, 2010 at 3:18 pm
This is a crack at it. Not really sure if it's what you need.
Forgive typos & assumptions.
select
i.[Image name]
,a.*
,d.*
from images i inner join ref r on i.ID=r.ID
left join Constituents a on r.ConstituentID = a.ConstituentID and ConstituentTypeID=1-- this joins the artiests
left join Constituents d on r.ConstituentID = d.ConstituentID and ConstituentTypeID=2-- this joins the donors
Craig Outcalt
February 17, 2010 at 3:42 pm
I tried, it is still giving duplicates, something like this:
Image1Constituent1Constituent1ImageNo213,
Image1Constituent2Constituent2ImageNo213,
instead of:
Image1Constituent1Constituent2ImageNo213
Why?
February 17, 2010 at 3:58 pm
You may need to use a distinct, group by or subquery to get the intended result.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 18, 2010 at 12:29 pm
I created view with query I have, it come to this:
Image1 Constituent1 ImageNo213,
Image1 Constituent2 ImageNo213
Is there easy way to merge column 2 into one, to look like this:
Image1 (Constituent1, Constituent2) ImageNo213
Thanks
Brano
February 18, 2010 at 12:55 pm
If I understand you question I believe that you could use the Substring Function and the Concatenation Operator to achieve the desired results.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy