&quot;how to&quot; left join ?

"how to" left join ?

Jamez

Table1

| catid | catname | caticon

5 | category5 | image5.jpg

Table2

| id | name | cat | price

1 | productx | category4 | 9.99
2 | productf | category2 | 8.99

3 | productd | category4 | 3.99

Now, what im trying to accomplish:
Display all categories that are used in Table2 with their icon picture from Table1.....

To get just the used categories i have to "select distinct cat from Table2" , but how do i join the caticon field from Table1 ?

I'm not familiar with the join features so if anyone could print the actual php query line that would be great 🙂

eoghain

Well it's not a PHP query it's an SQL query. But here is what you want to do:

SELECT DISTINCT t2.cat, t1.caticon
  FROM Table2 t2
 INNER JOIN Table1 t1 ON (t2.cat = t1.catid)

That's an INNER JOIN which is what you want to do if you know there is going to be an icon for every category, or you only want categories that have icons.

If you want all categorys weather they have an icon or not, and the icons for the ones that do have them you'd do a LEFT OUTER JOIN as shown below:

SELECT DISTINCT t2.cat, t1.caticon
  FROM Table2 t2
  LEFT JOIN Table1 t1 ON (t2.cat = t1.catid)

Jamez

Thanks for the reply, but I can't seem to get it to work. I'm trying to use the second code you pasted....

SELECT DISTINCT t2.cat, t1.caticon
FROM Table2 t2
LEFT JOIN Table1 t1 ON (t2.cat = t1.catid)

What is the t2, t1 ? just short for the table names? I would only select FROM "Table t2" ?

The real names are:
Table1 = cats
Table1 Field for category names = catname
Table1 Field for category icons url = caticon

Table2 = prods
Table2 Field for category names = cat

I tried this:

SELECT DISTINCT prods.cat, prods.price, cats.caticn
  FROM prods
  LEFT JOIN cats ON (prods.cat = cats.catname)

But its listing every category a ton of times...

nemonoman

Your query should be returning DISTINCT rows of information:

Each row:

prods.cat, prods.price, cats.caticn

should be different from the one above and below.

So you will see prods.cat numerous times...one for each price x one for each icon

Jamez

Thanks for the replies , I got that working perfectly, now i have a new problem on a different project im doing:

I'm working on a password database that a superadmin can enter passwords and logins into and then grant individual users access to different ones....

The problem I'm stumped on is that in the users view they will see "passwords they have been approved for" , "passwords they have requested" and then I have a drop down box to "make a new request" for a new password....

The only thing is i dont want duplicate requests going into the database, so i need to exclude any rows that they already have access to or have already requested... almost like the opposite of what a regular left join would do....

Here are the tables in the DB:

USERS (list of all users and their passwords)

ID | user | pass

LOGINS (list of all the logins they can request or see)

ID2 | site | login | pass

ACCESS (Sets who has access to what)

ID3 | sid | uid | acc

SID = (site id) ID2 from logins table
UID = (user id) ID from users
ACC = 0 if they request, 1 if approved

So i basically need to join all 3 tables in the select and exclude $user anywhere his ID shows up in the ACCESS table, and display only SITE from LOGINS that are not in SID from ACCESS where UID = $user

Anyone know how to right that query? 😕

Roger_Ramjet

Left join where the right hand table IS NULL. If you had read the manual about join syntax in the first place you would not need to ask this question now.