null query help

[andydeans]

hi,

i have been working on creating a view in my database:

CREATE OR REPLACE VIEW productnotgot( ProductID, Product, ClientID, ClientFirstName, ClientLastName, Email1, Tel1, Email2, Tel2, ProductSold) AS SELECT Product2ID, 'Product2', c.ClientID, c.App1FirstName, c.App1LastName, c.App1Email, c.App1Phone, c.App2Email, c.App2Phone, p.clients_ClientID
FROM clients c JOIN Product2 p on c.ClientID = p.clients_ClientID WHERE p.clients_ClientID IS NULL
GROUP BY ClientID
UNION SELECT  ProductID, 'Product1', c.ClientID, c.App1FirstName, c.App1LastName, c.App1Email, c.App1Phone, c.App2Email, c.App2Phone, p.clients_ClientID
FROM Product1 p JOIN clients c on p.clients_ClientID = c.ClientID  WHERE p.clients_ClientID IS NULL
GROUP BY ClientID

The query executes with no errors but is not displaying any data.

i want to basically show all the clients with a particular product in the database.

any help woudl be appreciated.

many thanks

[rch1231]

The first thing I see is the join states:
clients c JOIN Product2 p on c.ClientID = p.clients_ClientID WHERE p.clients_ClientID IS NULL

Which means join the clients table to the products table where the client id is equal to the product client id and the product client id is null

So the only time you would get results is if you selected a client with a NULL client id.

[andydeans]

thanks rodney, i tried mailing you but never heard back. anyway how can i get around this?

i am slightly confused as how to get the query to work. the query without it being a "Create VIew" works as a SELECT.

I just need to be able to create the view in which the clients and product table are joined but where the clientid in the products table is null it shows so the user can see who has not been sold what. any help?

thanks

[tesuji]

Hi andyjeans,

glad to meet you again.

... SELECT Product2ID, 'Product2', c.ClientID, c.App1FirstName, c.App1LastName, c.App1Email,c.App1Phone, c.App2Email, c.App2Phone, p.clients_ClientID
  FROM clients c JOIN Product2 p on c.ClientID = p.clients_ClientID 
    WHERE p.clients_ClientID IS NULL 
     GROUP BY ClientID
...

This is again wrong. All database systems but one stringently refuse execution of that wrong select-statement for it is a severe contradiction to relational algebra and set theory. However mysql let user be defining and executing such stuff.

We have already discussed the problem of incomplete group-by clause in earlier postings. You may also find something more here.

Btw, "WHERE p.clients_ClientID IS NULL" is formally correct, for it refers to a foreign key in an one-to-many relationship where NULL values are allowed on the many-side. However, it cannot be used that way in ON condition as rch1231 already stated. A primary key cannot be NULL (on the one-side)! Therfore no results.

-- tesu