Query - two times same table

Question

Post reply

Query - two times same table

branovuk

SSCommitted

Points: 1774
More actions
February 17, 2010 at 2:23 pm

#220708

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 1

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

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

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.

branovuk SSCommitted Points: 1774 More actions · Answer 3

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

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/

branovuk SSCommitted Points: 1774 More actions · Answer 5

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

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 6

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

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

branovuk SSCommitted Points: 1774 More actions · Answer 7

I tried, it is still giving duplicates, something like this:

Image1Constituent1Constituent1ImageNo213,

Image1Constituent2Constituent2ImageNo213,

instead of:

Image1Constituent1Constituent2ImageNo213

Why?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

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/

branovuk SSCommitted Points: 1774 More actions · Answer 9

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

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/