Join Query

[sipps]

Hi all,

I am building an internal site which manages our computers/users and what software is currently installed on each system.

My query is that I want to show all the copies of one particular piece of software, and whether they are installed on a computer (and which computer!).

So I have the basic 'product', 'product_line' tables and then an 'installation' table which holds the 'Product_Line_ID' and also the 'Computer_ID' from the 'computer' table.

I think I have to use a left outer join because I want to show all the product_line items of a particular product, and if they are installed, I want to show which computer they are on.

Here is the query that isn't working, but it should give you the general gist of what I am aiming towards.

SELECT *
FROM product, product_line, category, installation
WHERE product.Product_ID = product_line.Product_ID AND product.Product_ID = '$HTTP_GET_VARS[Product_ID]' AND product.Category_ID = category.Category_ID AND installation.Product_Line_ID = product_line.Product_Line_ID LEFT OUTER JOIN computer ON installation.Computer_ID = computer.Computer_ID

Please can someone help with this query as it's bugging me greatly!

Thanks

sipps

 

[sleipnir214]

The join clause must be used in the FROM clause, in lieu of <tablename>, <tablename>, <tablename>, not in the WHERE clause:

SELECT *
FROM
product p
LEFT JOIN
product_line pl
on p.<some column> = pl.<some column>
LEFT JOIN
category c
on pl.<some column> = c.<some column>
.
WHERE
<some criterion>



That should point you in the right direction. You didn't mention the categories table in your description of the problem, so I can't get more specific.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[sipps]

Thank you sleipnir214, I shall try that now and see what I come up with. Sorry for not mentioning the category table. Each product is of a certain category, i.e Operating System etc.

Thanks again,

sipps

 

[sipps]

It's strange, I know what I need in a way, but I can't write it!

I want to select all the product_line items of product type 1. Some of these line items may be associated with a computer, some may not so I want to show both.

I have written an outer join query to show all the computers, with and without users. I tried to follow the same sort of rule with this query. The computer display query was this:

SELECT computer.Computer_ID AS Comp_ID, Make, Model, Processor, Memory, Hard_Disk, DVD, CDRW, Monitor_Size, User_ID, User_Fname, user.Computer_ID
FROM computer LEFT OUTER JOIN user ON user.Computer_ID = computer.Computer_ID

So this shows all the computers and associated users, and computers if they have no users.

For this particular query, I want to show all the product_line items, that have been installed on a computer, or have not yet been installed.

My tables are computer, user, installation, product, product_line, category. One product fits into a category, there are several instances of a product so they are in product_line. Computer is stand alone. A user has a computer ID in the table. When a new installation is made, the product_line item is stored along with the computer it was installed to. (Hope this makes sense?!).

That is why I wrote:

SELECT * FROM product p, product_line pl, installation i, computer c WHERE p.Product_ID = pl.Product_ID AND pl.Product_Line_ID = i.Product_Line_ID AND i.Computer_ID = c.Computer_ID

But this only returns the product_line items that are associated with a computer, I need all items. I am not sure whether I need an outer join to get these?

Thanks again,

sipps

 

[sleipnir214]

If you want a row of a table to be returned regardless of whether there are related rows of a related table, then, yet, by definition you must use an outer join.

I thought we discussed this.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[sipps]

Sorry sleipnir214,

I am unsure what the error is here. This query is only bringing back the product_line records and not the related records of installation or computer. Is there something incorrect with the join queries I am using?

SELECT *
FROM product LEFT OUTER JOIN product_line ON product.Product_ID = product_line.Product_ID LEFT OUTER JOIN installation ON installation.Product_Line_ID = product_line.Product_Line_ID LEFT OUTER JOIN computer ON computer.Computer_ID = installation.Computer_ID
WHERE product.Product_ID = '$HTTP_GET_VARS[Product_ID]'

Thank you again for all your help,

sipps