Advanced SQL Help

Report · Apr 21, 2007

How would i write this SQL statement?

I have a table of products that i am trying to get. I have another table that holds relations for various things. In my SQL i am trying to get products that may have more than one relation in the second table. So, for example, say i want to get product ID 1. In my where statement, i also say ID 1 should have x relationship in the relations table AND y relation in the same table.... I am able to get it to work if i leave the y relation out. I am assuming that the SQL is treating these as two different records so it will only display either the x relation or y relation.

I need a work around and do not want use Unions as i would then get two different results.

Report · Apr 23, 2007

Can you be more specific about your question? Can you include the columns you are trying to retrieve and the relationship between those columns? For instance, in the relations table, do you have multiple columns that are storing the relationship back to the products table or multiple rows? Lastly what database are you using?

Report · Apr 23, 2007

Create a query for the x relationship. call it queryX

Create a query for the y relationship. call it queryY

Then create your product query and require that is have a valid join
to queryX AND QueryY

You should get valid results where the product has entries in both the
other queries.

On Sun, 22 Apr 2007 02:56:50 +0000 (UTC), "barbedwire103"
<webforumsuser@macromedia.com> wrote:

>How would i write this SQL statement?
>
> I have a table of products that i am trying to get. I have another table that
>holds relations for various things. In my SQL i am trying to get products that
>may have more than one relation in the second table. So, for example, say i
>want to get product ID 1. In my where statement, i also say ID 1 should have x
>relationship in the relations table AND y relation in the same table.... I am
>able to get it to work if i leave the y relation out. I am assuming that the
>SQL is treating these as two different records so it will only display either
>the x relation or y relation.
>
> I need a work around and do not want use Unions as i would then get two
>different results.

Bruce McKee
mm-help@phoenix-systems.com
For additional info on this, please send email to the
above addess.

Report · Apr 23, 2007

also, i know i said x and y relations in my original question but this was a simplification of my problem. There can actually infinite amount of relations in this table. So this will yeild infinite joins right...?

Report · Apr 23, 2007

so can this be done with a single select statement and a join? I dont see how this would work unless i made two completely seperate queries...

Thanks for the direction though!

Report · Apr 23, 2007

For whoever may visit this topic with a similar problem, i was able to find a fix by first gathering all the ID's of the things i would be testing for into an array and then use this array in the SQL statement with the use of an IN function.