Link records in tables

[En-Motion]

I'm currently linking two different tables in two different databases. One contains all the info from a product, while the othe contains information about the installation of the product. The SQL I use only returns those products that HAVE an entry in my install table, but many don't ie they were installed before installation notes were kept.
How do I get it that I can see all products and install notes for those that have an entry in install table. IO presume it's something to do with my 'AND' statement but not syre how to remedy it

SELECT prodmanage.user.id, prodmanage.widescreen_user.user_id, prodmanage.widescreen.name, prodmanage.user.username, prodmanage.user.password, prodmanage.widescreen.location, prodmanage.widescreen.serial_no, prodmanage.widescreen.last_access_date, prodmanage.widescreen.status, prodmanage.widescreen.proprietor, prodmanage.widescreen.phone, installation.install.id, installation.install.installer, installation.install.widescreen_id, prodmanage.widescreen.id, installation.install.install_notes, installation.install.status
FROM prodmanage.user, prodmanage.widescreen_user, prodmanage.widescreen, installation.install
WHERE prodmanage.user.id = prodmanage.widescreen_user.user_id AND prodmanage.widescreen.id = prodmanage.widescreen_user.widescreen_id AND installation.install.widescreen_id = prodmanage.widescreen.id
ORDER BY prodmanage.widescreen.name ASC

[tyson.crouch]

SELECT prodmanage.user.id, prodmanage.widescreen_user.user_id, prodmanage.widescreen.name, prodmanage.user.username, prodmanage.user.password, prodmanage.widescreen.location, prodmanage.widescreen.serial_no, prodmanage.widescreen.last_access_date, prodmanage.widescreen.status, prodmanage.widescreen.proprietor, prodmanage.widescreen.phone, installation.install.id, installation.install.installer, installation.install.widescreen_id, prodmanage.widescreen.id, installation.install.install_notes, installation.install.status

FROM prodmanage.user, prodmanage.widescreen_user, prodmanage.widescreen, installation.install

WHERE (prodmanage.user.id = prodmanage.widescreen_user.user_id) 
AND (prodmanage.widescreen.id = prodmanage.widescreen_user.widescreen_id) 
AND (installation.install.widescreen_id = prodmanage.widescreen.id)

ORDER BY prodmanage.widescreen.name ASC

I find that a lot of people seem to make the mistake of not closing off their WHERE clauses where the multiple expressions are mutually exclusive of one another. I find it very helpful as a rule of thumb to make sure that all of your where clauses are encapsulated (surrounded) by the parenthesis.

The where clause may be being misread by the DB engine.

Give this a quick try, if doesnt work. I'll have another look

Regards,
TC

[En-Motion]

Tried parenthesis but still same deal - only shows records with an entry in 'install' table, not all records. I think a JOIN might be what i'm after, but I'm not sure yet how to do it across 3 tables.

[tyson.crouch]

Could you please post the full create statements for all of the given tables
- prodmanager
-- User
-- widescreen
-- widescreen_user

- installation
-- install

This is just so i can get a better understanding of your entire schema structure.
Thx

[d5e5]

Tried parenthesis but still same deal - only shows records with an entry in 'install' table, not all records. I think a JOIN might be what i'm after, but I'm not sure yet how to do it across 3 tables.

It looks like you need a left join, but it's not clear to me for which table you want to list all rows, and which tables should be joined to lookup corresponding rows if they exist plus return rows of null fields where they do not (for example products that have no rows in the 'install' table.)

Example of left join for three tables