Jump to content

Archived

This topic is now archived and is closed to further replies.

dewdo

Array checking

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
$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.

Share this post


Link to post
Share on other sites
Don't know. Don't know you data or table structure. How are the 3 tables related?

Share this post


Link to post
Share on other sites
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 ....

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
thanks for helping. i apologize for not spelling it out more clearly earlier in the process.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
I can't see an obvious error. I haven't got MS SQL Server on my home PC so I cannot test.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.