Join problems - part two

[KevinM1]

Yeah, yeah, I know.  I thought I had it figured out, too.

 

To recap: my problem is that I want to print out all rows from three tables where the availability column of each table is equal to 'y' and, in the case of rows of two or more tables having the same value in their name columns, only one of them should be returned.  Admittedly, this would be far simpler if my databases were normalized.  Unfortunately, this project has mutated throughout its lifespan, so normalization was ignored in favor of "do it...now."  That being said, when given the opportunity, I will normalize this database, but I digress.

 

My tables have the following format, with the value 'table_name_id' as a stand-in for gaming_peripherals_id, office_peripherals_id, and peripherals_id respectively:

table_name_id TINYINT(3) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
price FLOAT,
availability CHAR(1),
description TEXT,
pic_url TEXT

 

Unfortunately, MySQL syntax is a weak point for me, so I'm at a loss on how to retrieve the information I want in the way specified above.  From my research, a NATURAL JOIN looks like it would do the trick (more or less, any way), but it's not available in my version of MySQL (4.1.92, I believe).  I got an INNER JOIN to work, but it was based on the id numbers of each table.  Unfortunately, this limits my results to the lowest number of rows found in the collection of tables.  And to be honest, LEFT/RIGHT JOINS are a bit...frightening with their syntax.

 

Any help would be greatly appreciated.  Thanks.

[Wildbug]

You could use the SELECT DISTINCT form.  That will cut out duplicate rows (relative to what you've requested from the query -- the column list).

 

SELECT DISTINCT peripherals.name FROM gaming_peripherals,office_peripherals,peripherals
WHERE gaming_peripherals.availability = 'y'
AND office_peripherals.availability = 'y'
AND peripherals.availability = 'y'
AND peripherals.name = office_peripherals.name
AND peripherals.name = gaming_peripherals.name;

 

What information, exactly, did you want to extract?

[Wildbug]

Actually, on a small mockup of your db structure, that query worked without the DISTINCT.  I think what was wrong with your originally posted query was that it used the not equal (!=) comparison to return every row with differing names, which, in a huge join, is alot of irrelevant data.  When you join two (or more) tables, every combination of rows is returned.  What you actually need to do is to make the equals (=) comparison between the names:

 

SELECT * FROM gaming_peripherals,office_peripherals,peripherals
WHERE gaming_peripherals.availability = 'y'
AND office_peripherals.availability = 'y'
AND peripherals.availability = 'y'
AND peripherals.name = office_peripherals.name
AND peripherals.name = gaming_peripherals.name;

 

Is that what you need?

[fenway]

A proper JOIN would be preferred here...

 

SELECT * FROM gaming_peripherals
JOIN office_peripherals ON ( peripherals.name = office_peripherals.name )
JOIN peripherals ON ( peripherals.name = gaming_peripherals.name )
WHERE gaming_peripherals.availability = 'y'
AND office_peripherals.availability = 'y'
AND peripherals.availability = 'y'

 

 

[KevinM1]

A proper JOIN would be preferred here...

 

SELECT * FROM gaming_peripherals
JOIN office_peripherals ON ( peripherals.name = office_peripherals.name )
JOIN peripherals ON ( peripherals.name = gaming_peripherals.name )
WHERE gaming_peripherals.availability = 'y'
AND office_peripherals.availability = 'y'
AND peripherals.availability = 'y'

 

I know this is a really late follow-up, but I'm getting a 1054 error when trying this query.  Namely, it says "#1054 - Unknown column 'peripherals.name' in 'on clause' "  Unfortunately, phpMyAdmin doesn't give any more error information than that.  I double checked my peripherals table and, yes, there is a name column, so this error is a mystery to me.

[Wildbug]

SELECT * FROM gaming_peripherals
JOIN office_peripherals ON ( peripherals.name = office_peripherals.name )...

 

Yeah, but you're joining gaming_peripherals and office_peripherals, NOT peripherals in that clause.

[KevinM1]

SELECT * FROM gaming_peripherals
JOIN office_peripherals ON ( peripherals.name = office_peripherals.name )...

 

Yeah, but you're joining gaming_peripherals and office_peripherals, NOT peripherals in that clause.

 

Unfortunately, changing it to gaming_peripherals.name yields me 0 returned rows, which is obviously not right.  I'm thinking I may just delete the redundancies and do a simpler join.

[Barand]

This sounds more a case for a UNION than a JOIN

<?php 
$query = "SELECT DISTINCT description FROM gaming_peripherals WHERE availability = 'Y'
UNION
SELECT DISTINCT description FROM office_peripherals WHERE availability = 'Y'
UNION
SELECT DISTINCT description FROM gaming_peripherals WHERE availability = 'Y' ";
?>

[Wildbug]

Are you sure it's wrong?  Maybe you really don't have any matching rows.  Both of these queries work for me:

 

SELECT * FROM
peripherals JOIN gaming_peripherals ON peripherals.name=gaming_peripherals.name
JOIN office_peripherals ON office_peripherals.name=peripherals.name
WHERE gaming_peripherals.availability = 'y'
AND office_peripherals.availability = 'y'
AND peripherals.availability = 'y';

// ...and...

SELECT * FROM gaming_peripherals,office_peripherals,peripherals
WHERE gaming_peripherals.availability = 'y'
AND office_peripherals.availability = 'y'
AND peripherals.availability = 'y'
AND peripherals.name = office_peripherals.name
AND peripherals.name = gaming_peripherals.name;

// ... both yield:

+----+--------+--------------+----+--------+--------------+----+--------+--------------+
| id | name   | availability | id | name   | availability | id | name   | availability |
+----+--------+--------------+----+--------+--------------+----+--------+--------------+
|  1 | thing1 | y            |  1 | thing1 | y            | 19 | thing1 | y            |
|  4 | thing4 | y            |  4 | thing4 | y            | 22 | thing4 | y            |
+----+--------+--------------+----+--------+--------------+----+--------+--------------+
2 rows in set (0.00 sec)

// ...from the following data:

mysql> select * from peripherals;
+----+--------+--------------+
| id | name   | availability |
+----+--------+--------------+
| 19 | thing1 | y            |
| 20 | thing2 | n            |
| 21 | thing3 | y            |
| 22 | thing4 | y            |
| 23 | thing5 | y            |
| 24 | thing6 | n            |
+----+--------+--------------+
6 rows in set (0.00 sec)

mysql> select * from gaming_peripherals;
+----+--------+--------------+
| id | name   | availability |
+----+--------+--------------+
|  1 | thing1 | y            |
|  2 | thing2 | n            |
|  3 | thing3 | n            |
|  4 | thing4 | y            |
|  5 | thing7 | y            |
|  6 | thing8 | n            |
+----+--------+--------------+
6 rows in set (0.00 sec)

mysql> select * from office_peripherals;
+----+--------+--------------+
| id | name   | availability |
+----+--------+--------------+
|  1 | thing1 | y            |
|  2 | thing2 | y            |
|  3 | thing3 | y            |
|  4 | thing4 | y            |
|  5 | thing5 | n            |
|  6 | thing9 | y            |
+----+--------+--------------+
6 rows in set (0.00 sec)

[KevinM1]

Are you sure it's wrong?  Maybe you really don't have any matching rows.  Both of these queries work for me:

 

SELECT * FROM
peripherals JOIN gaming_peripherals ON peripherals.name=gaming_peripherals.name
JOIN office_peripherals ON office_peripherals.name=peripherals.name
WHERE gaming_peripherals.availability = 'y'
AND office_peripherals.availability = 'y'
AND peripherals.availability = 'y';

// ...and...

SELECT * FROM gaming_peripherals,office_peripherals,peripherals
WHERE gaming_peripherals.availability = 'y'
AND office_peripherals.availability = 'y'
AND peripherals.availability = 'y'
AND peripherals.name = office_peripherals.name
AND peripherals.name = gaming_peripherals.name;

// ... both yield:

+----+--------+--------------+----+--------+--------------+----+--------+--------------+
| id | name   | availability | id | name   | availability | id | name   | availability |
+----+--------+--------------+----+--------+--------------+----+--------+--------------+
|  1 | thing1 | y            |  1 | thing1 | y            | 19 | thing1 | y            |
|  4 | thing4 | y            |  4 | thing4 | y            | 22 | thing4 | y            |
+----+--------+--------------+----+--------+--------------+----+--------+--------------+
2 rows in set (0.00 sec)

// ...from the following data:

mysql> select * from peripherals;
+----+--------+--------------+
| id | name   | availability |
+----+--------+--------------+
| 19 | thing1 | y            |
| 20 | thing2 | n            |
| 21 | thing3 | y            |
| 22 | thing4 | y            |
| 23 | thing5 | y            |
| 24 | thing6 | n            |
+----+--------+--------------+
6 rows in set (0.00 sec)

mysql> select * from gaming_peripherals;
+----+--------+--------------+
| id | name   | availability |
+----+--------+--------------+
|  1 | thing1 | y            |
|  2 | thing2 | n            |
|  3 | thing3 | n            |
|  4 | thing4 | y            |
|  5 | thing7 | y            |
|  6 | thing8 | n            |
+----+--------+--------------+
6 rows in set (0.00 sec)

mysql> select * from office_peripherals;
+----+--------+--------------+
| id | name   | availability |
+----+--------+--------------+
|  1 | thing1 | y            |
|  2 | thing2 | y            |
|  3 | thing3 | y            |
|  4 | thing4 | y            |
|  5 | thing5 | n            |
|  6 | thing9 | y            |
+----+--------+--------------+
6 rows in set (0.00 sec)

 

Both queries give me 0 rows, but I know I have rows in different tables with the same name:

 

SELECT peripherals_id, name, availability FROM peripherals WHERE peripherals_id=13;

 

peripherals_id                                            name                                        availability

13                                    Microsoft Black PS/2 Keyboard Optical Mo                y

 

SELECT gaming_peripherals_id, name, availability FROM gaming_peripherals WHERE gaming_peripherals_id=1;

 

gaming_peripherals_id                                  name                                        availability

1                                      Microsoft Black PS/2 Keyboard Optical Mo                y

 

As an aside, is there any way to have phpMyAdmin print out query results in the ASCII format you used?  It'd no doubt look better than my mangled attempt, and it'd make it easier to display all of my table info at once.

[KevinM1]

This sounds more a case for a UNION than a JOIN

<?php 
$query = "SELECT DISTINCT description FROM gaming_peripherals WHERE availability = 'Y'
UNION
SELECT DISTINCT description FROM office_peripherals WHERE availability = 'Y'
UNION
SELECT DISTINCT description FROM gaming_peripherals WHERE availability = 'Y' ";
?>

 

At first glance, it looks like this works...thanks!

 

EDIT: d'oh!  Now I'm getting repeated rows (ironically, the Microsoft keyboard/mouse combo that I referenced in the post above).

[Wildbug]

Oh, so you didn't really need availability to ='Y' in all rows?  :-\

 

The SELECT DISTINCT will get distinct rows from each table, but not distinct relative to the union.  I think it should be:

 

SELECT DISTINCT description FROM gaming_peripherals WHERE availability = 'Y'
UNION DISTINCT SELECT description FROM office_peripherals WHERE availability = 'Y'
UNION DISTINCT SELECT description FROM gaming_peripherals WHERE availability = 'Y';

[KevinM1]

Oh, so you didn't really need availability to ='Y' in all rows?  :-\

 

The SELECT DISTINCT will get distinct rows from each table, but not distinct relative to the union.  I think it should be:

 

SELECT DISTINCT description FROM gaming_peripherals WHERE availability = 'Y'
UNION DISTINCT SELECT description FROM office_peripherals WHERE availability = 'Y'
UNION DISTINCT SELECT description FROM gaming_peripherals WHERE availability = 'Y';

 

Unfortunately, I'm still getting repeated rows.  Like I tried (horribly) to show above, as an example row 13 in peripherals and row 1 in gaming_peripherals have the same info across the board.  Instead of just having one of those two rows (it doesn't matter which one for my script) returned, I get both back.

[Wildbug]

(You can use code tags to preserve characters spacing and use monospaced font.)

 

For the DISTINCT part to work, the entire row must be unique.  If you're returning the id as part of the query, and the ids are different, then both rows will be included and the description will be a duplicate.  The above query works as printed; what are you using?  Show me exactly the query you are using.

[bubblegum.anarchy]

FYI - UNION performs a distinct by default.

[KevinM1]

For the DISTINCT part to work, the entire row must be unique.  If you're returning the id as part of the query, and the ids are different, then both rows will be included and the description will be a duplicate.

 

I think this is the problem as I haphazardly put items into the different tables.  So, to use the Microsoft keyboard/mouse combo example, in the peripherals table it has an id of 14.  In the gaming_peripherals table, it has an id of 1. Unfortunately, my scripts require the id of each item.

 

In any event, it doesn't matter much now as this project has been put on the backburner for a bit.  If/when I come back to it, I think I'll just redesign the tables from the ground-up.  I never thought I needed to normalize my tables when this project started months ago as I thought it would remain small, but it kinda blew up on me and caught me with my pants down.