multi table query retrieving too many results

[lazzerous]

I know my query is wrong.

I have three tables that all have a field called table_ids
Each row of each table has unique, non-incremental values in the table_ids field

I am trying to make a single query that will take a table_id number and find which table it came from, along with which row in that table it came from.

my current query looks like this:

select
 cat_product_line_info.product_line_id as pl_id,
 cat_sub_product_line_info.sub_product_line_id as sub_pl_id,
 cat_item_info.item_id as item_id
 from
 cat_product_line_info,
 cat_sub_product_line_info,
 cat_item_info
 where
 cat_product_line_info.table_ids like '%128,%'
 or
 cat_sub_product_line_info.table_ids like '%128,%'
 or
 cat_item_info.table_ids like '%128,%';

This returns about 300 rows, and the only way I know which is right is that one columns has the same value every time.

ex:

+-------+-----------+---------+
| pl_id | sub_pl_id | item_id |
+-------+-----------+---------+
|    14 |        11 |     137 |
|    15 |        11 |     137 |
|    20 |        12 |     137 |
|    21 |        12 |     137 |
|    23 |        12 |     137 |

This tells me that the cat_item_info table holds that table_id, and the id of that entry is 137.

This still causes too much work

I want to have one line returned like this:

+-------+-----------+---------+
| pl_id | sub_pl_id | item_id |
+-------+-----------+---------+
|       |           |     137 |
+-------+-----------+---------+

Anyone know how I can accomplish this?

Thank you!
-=Lazzerous=-

[nemonoman]

You're trying to accomplish an Outer Join or Left Join. Read up on Outer/Left Joins in your manual. Syntax is different depending on your SQL dialect, but the concept is always the same.

[lazzerous]

The documentation I am finding throgh the mysql manual isn't giving much explination on what joins do...

Do you know of any other sources that would give more complete information, along with better examples?

Thank you!

[lazzerous]

ok...

i read a couple tutorials on outer joins, inner joins, left outer joins, and right outer joins.

These are all dependant on the tables containing matching data.

Of the three tables I am trying to compare, only one will have the matching data I am looking for - I just need to know which row in which table.

lets look at three test entries

we'll say each table has one row of data in it
we'll also say each table only has two fields, ID, and TABLE_IDS

cat_product_line_info:
----------------------------
id int(3) unique auto_increment
table_ids varchar(11)

car_sub_product_line_info:
----------------------------------
id int(3) unique auto_increment
table_ids varchar(11)

cat_item_info:
------------------
id int(3) unique auto_increment
table_ids varchar(11)

now one, and onlyt one of the entries of one of those tables will have a value of '122'

I need a single query that will tell me which table, and which row has it.

I don't see how the join syntax within mysql can give me that information.

I also fully admit that I may have missed something somewhere and I'm just being stupid. It happens to us all, and quite frequently to me.

Help?
Thank you again
-=Lazzerous=-

[nemonoman]

SELECT cat_product_line_info.id, car_sub_product_line_info.id, cat_item_info.id

FROM cat_product_line_info LEFT JOIN car_sub_product_line_info.id ON cat_product_line_info.id=car_sub_product_line_info.id
LEFT JOIN cat_item_info ON car_sub_product_line_info2.id=cat_item_info.id
WHERE
cat_product_line_info.table_ids LIKE '%128,%'
OR
cat_sub_product_line_info.table_ids LIKE '%128,%'
OR
cat_item_info.table_ids LIKE'%128,%';

[lazzerous]

ok, using the actual fields i have for my tables, your query ended up looking like this:

SELECT
cat_product_line_info.product_line_id,
cat_sub_product_line_info.sub_product_line_id,
cat_item_info.item_id
FROM
cat_product_line_info
LEFT JOIN
cat_sub_product_line_info
ON
cat_product_line_info.product_line_id=cat_sub_product_line_info.sub_product_line_id
LEFT JOIN
cat_item_info
ON
cat_sub_product_line_info.sub_product_line_id=cat_item_info.item_id
WHERE
cat_product_line_info.table_ids
LIKE
'%128,%'
OR
cat_sub_product_line_info.table_ids
LIKE
'%128,%'
OR
cat_item_info.table_ids
LIKE
'%128,%';

which returned no results.
however, I know that there is a row within cat_sub_product_line_info that has that entry.

Where am I going wrong?

Thank you again,
-=Lazz=-

[nemonoman]

Before I get too spun up, could you please verify that

cat_sub_product_line_info.table_ids LIKE '%128,%'

is what you meant to say?

Could you please verify that you meant to include a comma?

Could you please successfully find one row in one table with

cat_sub_product_line_info.table_ids =128

i.e, not like, etc., and try that as the OR part of your query?

Thanks.

Nemo,
Often frustrated when what I think is wrong with one of my queries is actually something wrong with my understanding ofthe data.

[lazzerous]

nemo, sorray about that

yes, it should have a comma
each table_ids field stores up to three id's, so an entry might look like this: '122,147,19,'

I can successfully find which of the three tables has that ID using three queries:

select product_line_id from cat_product_line_info where table_ids LIKE '%128,%';

select sub_product_line_id from cat_sub_product_line_info where table_ids LIKE '%128,%';

select item_id from cat_item_info where table_ids LIKE '%128,%';

Then I'll use the value from the query that comes back with a result, two of them wont, as each table)id is used only once, for one entry in one of these tables.

But I want to be able to do this with one query.

I apologize again for not explaining myself better.

If there's anything else not making sense, I'll make sure I explain it further.

Thank you,
-=Lazz=-

[mtimdog]

Given the uniqueness of your question, have you considered further normalizing your database?😕

If you'd like to do this in 3 queries

$table='';
if (1==mysql_num_rows(mysql_query("select id from table1 where id = '%128,%'")){
    $table='table1';
}elseif(1==mysql.....){
    $table='table2';
}elseif(1==mysql....){
    $table='table3';
}
//3 quick queries that let you know which table it was in if found

Also, since you're storing ids in a list...try the mysql function in ()
like

select id from table1 where $var in id_list

[nemonoman]

After thinking about this off and on for the night and trying a number of different strategies, I realized at last that the difficulty lay in the structure of the the problem you presented.

With an extremely complex query with multiple subqueries it might be possible to return the specific result set you desire.

HOWEVER, that query would be SO tricky that with your current skill set I sincerely doubt that you could adapt or debug it.

What's going on here is that your desire is not consistent with your data design. You need either to change your desire or your design.

You have created three tables, supposedly for three different types of entities:

cat_product_line_info
cat_sub_product_line_info
cat_item_info

These tables have NO relationship. There is NO FOREIGN KEY IDENTIFIER to support the sort of output you want.

To make the design flaw clearer, I'll rename them:

Movie_title
Annoying_Habit
Sugary_Cereal

What's the 'glue' that ties these tables together??

Nothing.

You could create a glue by creating another table: in your case I'll call it 'product':

Product would be simply a list of all ids that might be found in cat_product_line_info or cat_sub_product_line_info or cat_item_info

Now your query is a fairly simple outer join,

SELECT * from FROM product LEFT JOIN cat_product_line_info ON cat_product_line_info.id=product.id LEFT JOIN car_sub_product_line_info.id ON cat_product_line_info.id=car_sub_product_line_info.id
LEFT JOIN cat_item_info ON car_sub_product_line_info2.id=cat_item_info.id
WHERE product.id = somenumber

See, with this query, you don't need any ORs.

Your design flaw is that you intuit a table relationship to product ids, but you have not explicity created that relationship.

That said, may I further suggest that in the future you design such tables differently:

It appears that you don't necessarily have distinct data entities of cat_product_line_info or cat_sub_product_line_info or cat_item_info.

Since (apparently) the same product identifier will appear in ony one of these tables, it suggests that cat_product_line_info, cat_sub_product_line_info and cat_item_info are ATTRIBUTES of a single entity, say a 'PRODUCT' entity.

So you might -- you SHOULD -- create a SINGLE table, "PRODUCT" and give one column the attribute 'info', and populate each product id with some identifier like 'cat_item', 'cat_product_line' or 'cat_sub_product_line'.

I hope this all makes sense.