page names with child

[letimati]

Hi I am wanting to get one record from a table on the condition of two variables but there is a chance the two conditions will be met so i want to give one preference over the other, sorry that has not explaned it well.

Example to help.

I have a module table which has pages and parent/children relationship

page_id name parent
1.1 home 1
1.2 info 1
1.2.3 product1 1.2


So I have a possibillity of two variables $id1 and $id2. (these come form some mod_rewrite rules). Now the first one is always going to be in this table but the second may be not a child node but something else. So i want to check if $id2 is in the module table and its parent is $id1, easy!

SELECT a. *
FROM module a, module b
WHERE a.name = $id2 && b.name = $id1 & a.parent = b.id

but i would like to find out if this was not true then just get the row for name = $id1 meaning the first id is the page we are on and the second id is not a page.

I thought somthing like this

SELECT a. *
FROM module a, module b
WHERE (a.name = $id2 && b.name = $id1 & a.parent = b.id)
OR
(a.name = $id1 && a.parent = 1)

but this gives me back as many rows as is in the module table althought they are all the correct row and i could just limit o, 1 and that would be fine but would like it to be perfect in case this causes bugs in the future.

thanks in advance for any other ideas on how to do this.

 

[r937]

since you posted in the ANSI SQL forum and not the MySQL forum, the first thing we need to do is to rewrite your query to conform to ANSI SQL

SELECT a.*
FROM module a
INNER
JOIN module b
ON a.parent = b.id
WHERE a.name = $id2
AND b.name = $id1

now, if this is "not true" then you want to "just get the row for name = $id1"

that would be like this --

SELECT a.*
FROM module a
WHERE a.name = $id1
AND a.parent = 1

so the way to get them both is simply to UNION them --

SELECT a.*
  FROM module a
INNER
  JOIN module b
    ON a.parent = b.id
 WHERE a.name = $id2
   AND b.name = $id1 
UNION
SELECT a.*
  FROM module a
 WHERE a.name = $id1
   AND a.parent = 1

r937.com | rudy.ca