Skip to main content
Participating Frequently
Apr 22, 2007 2:56 AM UTC
Question

Advanced SQL Help

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.
This topic is closed to new replies. Start a new post to keep the conversation going.

5 replies

Participating Frequently
Apr 23, 2007 11:08 PM UTC
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.
Translate
Participating Frequently
Apr 23, 2007 9:58 PM UTC
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!
Translate
Inspiring
Apr 23, 2007 7:26 PM UTC
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.
Translate
Participating Frequently
Apr 23, 2007 10:06 PM UTC
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...?
Translate
Apr 23, 2007 2:04 PM UTC
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?
Translate