mySQLquery question

@NedalsMar 07.2004 — #The link Khalid Ali gave you explains things quite well but, in practice, there are a few tricks available when using mySQL. As Khalid Ali mentioned JOINs are used when dealing with 2 or more related tables. In your case the tables are independant and will use seperate querys.

RE: the link

You seldom, if ever, will need INNER JOIN, do this instead..

'SELECT s.invnum, c.custname FROM sales s, customer c WHERE s.invnum=$invnum AND c.custid=s.cid';

(Note: In perl you don't need to concat the variable. This may not be true for PHP)

Some tricks here:

1. Don't use '*'. As you expand your database, you may decide to add an extra column. When you do, all your '*' queries will need to be edited.

2. 'sales s' is a shorthand way of writing 'sales AS s'. You can now use the 's' to identify which table the column belongs to. You may have read that, as long as the column names are unique, you don't need to do this BUT do it anyway. It's a good habit to get into.

LEFT JOIN is the only one you really need. A RIGHT JOIN can be coverted to a LEFT join by swapping the joined table with the FROM table.

'SELECT s.invnum, c.custname, v.via

FROM sales s, customer c, v.via

WHERE s.invnum=$invnum AND c.custid=s.cid AND v.invnum=s.invnum';

Suppose you had this and 'via' was NULL for a couple of items in your sales table, you would get no result for those invnums

to solve this, the LEFT JOIN is used

'SELECT s.invnum, c.custname, v.via

FROM sales s, customer c

LEFT JOIN via v ON v.invnum=s.invnum

WHERE s.invnum=$invnum AND c.custid=s.cid AND';

Now all the invnums will be returned along with blank fields for those NULL 'via's

Hope that helps a little.

mySQLquery question

2 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

mySQLquery question

2 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

2 Comments(s) _↴

Also in #PHP _↴