How do I combine these statements like this

[gregsmith]

Hi there,

I'm a semi-newb at mysql, so please bear with me.

I've got two statements that I want to combine but I can't figure out a way to do it with join or union:

[code]Statement 1: SELECT page_name, page_id, order_num FROM pages WHERE cat_id = '' AND sub_id = ''
Statement 2: SELECT cat_name, cat_id, order_num FROM categories[/code]

Here are the columns I want in the result set:

[code]page_name | page_id | cat_name | cat_id | order_num[/code]

with a field left blank if it doesn't exist in that table.

Union will combine them but obviously with the cat_name and cat_id results being listed under the page_name and page_id columns in the results set so I can't differentiate between pages and categories.

Joins seemed my best bet (I've only just looked them up) but I can't figure out how to get the results I want with the (WHERE cat_id = '' AND sub_id = '' ) bit to apply just to the pages table.  I'm guessing I can do something using pages.cat_id and pages.sub_id but my sql knowledge isn't good enough to work it out.

Any help greatly appreciated.

[mjlogan]

I am a little confused over what you have written, but are you after something like this.

[code]
SELECT * FROM pages, categories WHERE pages.cat_id = '' AND pages.sub_id = '' AND categories.order_num = pages.order_num
[/code]

[gregsmith]

Thanks for the reply.  I figured my post might be a little confusing.

It's sorta like that but order_num will never be the same between the two tables in this query.  The order_num is the cascading order in which they are going to be displayed.

I think this is what I want to do but it is returning a 'mysql_fetch_array(): supplied argument is not a valid MySQL result' style error:

[code]SELECT pages.page_id, pages.page_name, categories.cat_id, categories.cat_name, order_num FROM pages, categories WHERE pages.cat_id = '' AND pages.sub_id = ''
ORDER BY order_num ASC[/code]

This also returns the same error:

[code]SELECT * FROM pages, categories WHERE pages.cat_id = '' AND pages.sub_id = '' ORDER BY order_num ASC[/code]

[fenway]

I dion't see any ids.

[gregsmith]

[quote author=fenway link=topic=116999.msg478093#msg478093 date=1165186530]
I dion't see any ids.
[/quote]

Yeah, that's the point, I want to select the ones where the ID is blank.  I know what I want I just don't know how to ask for it.

[gregsmith]

OK, I've changed the database today and I'm still having trouble putting a query together.  Hopefully it'll make a little more sense now.

I basically want to ask now:
[code]
SELECT page_name, page_id, order_num FROM pages WHERE parent_id = '0'

SELECT cat_name, cat_id, order_num FROM categories WHERE parent_id = '0'[/code]

and I want the results ordered by:

[code]ORDER BY order_num ASC[/code]

The tables are like this:

pages:

[code]page_id, parent_id, page_name, page_contents, order_num[/code]

categories:

[code]cat_id, parent_id, cat_name, order_num[/code]

The 'order_num' field denotes the order in which they'll be listed in the menu, the parent_id is the id of the container category.

I want an sql statement to provide me with a result something like this:

[table]
[tr]
[td]page_id |[/td]  [td]page_name |[/td]  [td]cat_id |[/td]  [td]cat_name |[/td]  [td]order_num |[/td]
[/tr]
[tr]
[td]10[/td]  [td]page10[/td]  [td][/td]  [td][/td]  [td]1[/td]
[/tr]
[tr]
[td][/td]  [td][/td]  [td]15[/td]  [td]cat10[/td]  [td]2[/td]
[/tr]
[tr]
[td]11[/td]  [td]page11[/td]  [td][/td]  [td][/td]  [td]3[/td]
[/tr]
[tr]
[td]12[/td]  [td]page12[/td]  [td][/td]  [td][/td]  [td]4[/td]
[/tr]
[tr]
[td][/td][td][/td][td]16[/td][td]cat_11[/td][td]5[/td]
[/tr][/table]
[table][tr][td]

I hope this makes more sense now.

[craygo]

OK your query would look like so. I am not sure if you are doing this with php but here is some code. if not the sql statement can be used where ever.

[code]<?php
$sql = "SELECT * FROM pages JOIN catagories ON pages.parent_id = catagories.parent.id WHERE pages.parent_id = '0' ORDER BY order_num ASC";
  $res = mysql_query($sql) or die (mysql_error());
    while($r = mysql_fetch_assoc($res)){
extract($r);
echo "$page_id $page_name $cat_id $cat_name $order_num<br>/n";

}
?>[/code]

Ray
   

[gregsmith]

Thanks for the reply Ray,

I am using PHP yeah.  I put the query into phpmyadmin, and first of all got:

[code]#1052 - Column 'order_num' in order clause is ambiguous [/code]

Then I took out the ORDER BY bit and it gave me a result.  It joined it all together however with stuff from the 'pages' table on the same row as the 'categories' table.  I need each row in the result to be either from the pages or the categories table, just for them to be in the right order denoted by order_num across the tables.  I'd like any unincluded columns in the row to just be blank.

Here's a snippet of what I got off of phpmyadmin:

[code]page_id  parent_id  page_name page_contents page_desc page_keywords order_num cat_id parent_id cat_name order_num
42     0         pagetest      [BLOB - 4 B] [BLOB - 0 B]   [BLOB - 0 B]     2                   23     0             asdfdfffff 6
42     0         pagetest      [BLOB - 4 B] [BLOB - 0 B]   [BLOB - 0 B]     2                   22     0             category       3
42     0         pagetest      [BLOB - 4 B] [BLOB - 0 B]   [BLOB - 0 B]     2                   21     0             cat1         1
44     0         asdfasdfffff [BLOB - 4 B]      [BLOB - 0 B]    [BLOB - 0 B]      5           23     0               asdfdfffff 6[/code]

Does this give you any other ideas?

[craygo]

Well if that's the case you do not want to join the tables together you just want a query of all the blank parent_id's. You might like to know "blank" and "0" are 2 different things. you would have to physically have a 0 in the field for this query to work. if there is nothing in the field then you need to use
[code]WHERE pages.parent_id IS NULL[/code]

You would query each table seperately to just find the blank rows.

Ray

[gregsmith]

Nah, that gives me the same result, just ordered by page.order_num.  I need to be able to distinguish whether a row is from the categories table or pages table - at the moment the data on each row seems to be from both tables jammed togther.

Any more ideas?

[craygo]

You have to seperate the queryies for each table. You can't put them together for your case. You just want to get the rows in one table.
So if you are using phpmyadmin just query the one table and get the results for that table then do the other table.

In php just have two queries

[code]<?php
echo "<p align=center><h1>Pages Table</h1></p>";
$psql = "SELECT page_name, page_id, order_num FROM pages WHERE parent_id = '0' ORDER BY order_num ASC";
$pres = mysql_query($psql) or die (mysql_error());
while($pr = mysql_fetch_assoc($pres)){
extract($pr);
echo "$page_id, $parent_id, $page_name, $page_contents, $order_numn<br>/n";
}

echo "<p align=center><h1>Catagory Table</h1></p>";
$csql = "SELECT cat_name, cat_id, order_num FROM categories WHERE parent_id = '0' ORDER BY order_num ASC";
$cres = mysql_query($csql) or die (mysql_error());
while($cr = mysql_fetch_assoc($cres)){
extract($cr);
echo "$cat_id, $parent_id, $cat_name, $order_num<br>/n";
}
?>[/code]

Ray




Ray

[gregsmith]

Yeah man, check the post in the php help forum.  This is exactly what I've also tried.

The fact is at some point either using an SQL statement or PHP I need to list the categories and the tables TOGETHER by order number.  This is the whole problem.  I can't figure out the best way to do it.

[fenway]

Sounds like you need a UNION statement.

[gregsmith]

Roopurt18 has sorted me out in the other thread.  Thanks all.