Array checking

[dewdo]

I have a table that has a complete set of labels. I have another table that assigns some labels to some users. I want to create a list that prints the complete set of labels and then says whether it has been assigned to the user or not.

table one:
label 1
label 2
label 3
label 4

table two:
user1 | label 1
user2 | label 2

desired output:
label 1 | user1 | yes
label 2 | user1 | yes
label 3 | user1 | no
label 4 | user1 | no

any ideas?

thanks

[Barand]

I can't help you with your sql as there are no column names, but this looks an identical problem to this one

http://www.phpfreaks.com/forums/index.php/topic,107845.msg436674.html#msg436674

[dewdo]

i dont see how that link gets me there. thanks for searching...

[Barand]

[code]<?php
$username = 'user1';
$sql = "SELECT a.label, b.label
        FROM `table one` a
        LEFT JOIN `table two` b
            ON a.label = b.label AND b.user = '$username'";
$res = mysql_query($sql) or die(mysql_error());
echo '<pre>';
while (list($laba, $labb)=mysql_fetch_row($res)) {
    $assign = $labb ? 'yes' : 'no';
    printf ('%-8s | %-8s | %-4s<br>', $laba, $username, $assign);
}
echo '</pre>';
?>[/code]

[dewdo]

thank you, that was helpful.

If there are three tables [ptypes], [cparties], [eligible]

ptypes have all of label1 and cparties have all of label2, eligible matches some of label1 and label2. I need to show a complete list such that there is all ptypes and all cparties and then a yes/no based on what is in eligible ....

would this be a union or embedded left join of some sort?

[dewdo]

$sql = "SELECT DISTINCT tb1.label, tb2.label2 FROM tb1, tb2, tb3 WHERE '(SELECT tb2.label2 FROM tb2 LEFT JOIN tb3 ON tb2.label2 = tb3.label2)'";

why wont this work?

I am trying to display the entire contents of two tables and then show which of the rows match are recorded together in a third table.

[Barand]

Don't know. Don't know you data or table structure. How are the 3 tables related?

[dewdo]

label2.

I am trying to achieve a list where every type of car is shown (table 1), every possible color is shown (table 2) and then a column is inserted that says whether that car comes in that color (table 3). Except, table 3 just has entries where a car does come in that color. I need the results to yes, if its in table three, or no if its not in table three ....

[Barand]

So I guess this is what you have?
[pre]
car (T1)              car_color (T3)        color (T2)
---------              --------------        ----------
carID          -----  carID            +--- colorID
car_decription        colorID    ------+    color


1 | BMW 525            1 | R                R | Red
2 | Merc SL500        1 | S                G | Green
3 | Trabant            2 | S                B | Blue
                      3 | G                S | Silver
                      3 | B
[/pre]

And this is what you want?                     
[pre]

          | Red  |  Green | Blue | Silver |
-----------+-------+--------+------+--------+
BMW 525    |  Y    |        |      |  Y    |
Merc SL500 |      |        |      |  Y    |
Trabant    |      |    Y  |  Y  |        |
[/pre]

[dewdo]

yeah. thats right.

[Barand]

If you need help with queries, giving that sort of info in the first place helps us to help you. I'll set up some test tables and see what I can come up with.

[dewdo]

thanks for helping. i apologize for not spelling it out more clearly earlier in the process.

[Barand]

OK. This query will do it.

Note that when joining the car and color tables I do not specify a join condition. This combines every value in car table with every value in color table.

I then LEFT JOIN to the car_color table so that if a match is found for the car.color combination then cc.carID has a value, but if no match is found then it contains NULL
[code]
SELECT c.car_desc, co.color, cc.carID
FROM car c INNER JOIN color co
LEFT JOIN car_color cc ON c.carID = cc.carID AND co.colorID = cc.colorID
ORDER BY c.carID, co.colorID
[/code]

gives -->
[pre]
car_desc  |color  | carID
-----------+-------+-------------
BMW 525    |Blue  |
BMW 525    |Green  |
BMW 525    |Red    |  1
BMW 525    |Silver |  1
Merc SL500 |Blue  |
Merc SL500 |Green  |
Merc SL500 |Red    |
Merc SL500 |Silver |  2
Trabant    |Blue  |  3
Trabant    |Green  |  3
Trabant    |Red    |
Trabant    |Silver |
[/pre]

[dewdo]

looks like it will work. thanks for the help!

[dewdo]

that query did not end up working ...

i have this structure

I have three tables

AllSTypes                          AllCPS                 
|types|                            |CPS|                 
type 1                              CP 1
type 2                              CP 2
type 3                              CP 3
type 4                              CP 4



AllCombo
|CPS|      |types|      |APSID|
CP 1        type 2        1
CP 2        type 1        2
CP 2        type 2        3
CP 4        type 3        4


I need results that show a full list of Types and CPS and then says whether there is an entry for that combo in AllCombo or not... that part i think we agree on

[Barand]

Same basic query different table and field names
[code]
SELECT t.types, c.cps, IF(m.apsid IS NULL, 'no', 'YES') AS matched
FROM allstypes t INNER JOIN allcps c
LEFT JOIN allcombo m ON t.types = m.types AND c.cps = m.cps
ORDER BY t.types, c.cps [/code]

[dewdo]

Incorrect syntax near the keyword 'IF'.

Line 1: Incorrect syntax near ','.

[Barand]

1 ) are you using MySQL?

2 ) post the code you are using as I ran the code prior to posting giving

[code]
<html>
<head>
<title>Query</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<table border=1 cellspacing=1 cellpadding=0><tr>
<th>types</th><th>cps</th><th>matched</th></tr>
<tr>
<td>type 1</td><td>CP 1</td><td>no</td></tr>
<tr>
<td>type 1</td><td>CP 2</td><td>YES</td></tr>
<tr>
<td>type 1</td><td>CP 3</td><td>no</td></tr>
<tr>
<td>type 1</td><td>CP 4</td><td>no</td></tr>
<tr>
<td>type 2</td><td>CP 1</td><td>YES</td></tr>
<tr>
<td>type 2</td><td>CP 2</td><td>YES</td></tr>
<tr>
<td>type 2</td><td>CP 3</td><td>no</td></tr>
<tr>
<td>type 2</td><td>CP 4</td><td>no</td></tr>
<tr>
<td>type 3</td><td>CP 1</td><td>no</td></tr>
<tr>
<td>type 3</td><td>CP 2</td><td>no</td></tr>
<tr>
<td>type 3</td><td>CP 3</td><td>no</td></tr>
<tr>
<td>type 3</td><td>CP 4</td><td>YES</td></tr>
<tr>
<td>type 4</td><td>CP 1</td><td>no</td></tr>
<tr>
<td>type 4</td><td>CP 2</td><td>no</td></tr>
<tr>
<td>type 4</td><td>CP 3</td><td>no</td></tr>
<tr>
<td>type 4</td><td>CP 4</td><td>no</td></tr>
</table>
</body></html>[/code]

[dewdo]

this is MS SQL. will it not work?

[Barand]

Why the **** are you posting in the MySQL forum then?

Try
[code]
SELECT t.types, c.cps, CASE WHEN m.apsid IS NULL THEN 'no' ELSE 'YES' END AS matched
FROM allstypes t INNER JOIN allcps c
LEFT JOIN allcombo m ON t.types = m.types AND c.cps = m.cps
ORDER BY t.types, c.cps[/code]

[dewdo]

My apologies, i just replied to my last message on this thread b/c it was a smilar problem.

I made the modification you suggested by get "Incorrect syntax near the keyword 'ORDER'." I did a search but did not see the answer ... do you have any thoughts?

[Barand]

I can't see an obvious error. I haven't got MS SQL Server on my home PC so I cannot test.