I have two tables. The first table is a list of codes. When one of these codes is used a record is created in a second table with a status next to it. A status of 0 means it has been added and is being considered, a status of 1 means it has been cancelled, and status of 2 means it was actually used. I want to write a query to get a list of codes. The codes in table 2 that have a status of 2 I want at the top of my result set. All the rest of the codes I want to be after that list.

Table 1 (TC)
Code
--------
100
200
300
400
500

Table 2 (SP)
Code          Status
---------------------------
100             0
200             2
200             1
400             2
400             2
500             1
500             2

The result would look like this:
Code            Status
-----------------------------
200               2
400               2
500               2
100               0
300               0

Here is my first take at the sql. The problem is that if a code is in table one with a status other than 2 it won't show up in either list:
(Select "Code", "Status", '1SP' as "Source" from SP where "Status" = 2 group by "Code")
union all
(Select "Code", 0 as "Status", '2TC' as "Source" from TC left join SP on TC."code" = SP."code" where SP."code" is null)
order by "Source" asc, "Code" asc

To try to resolve it I added the or clause, but that just causes duplication of results in my second sql statement
(Select "Code",  "Status", '1SP' as "Source" from SP where "Status" = 2 group by "Code")
union all
(Select distinct "Code", 0 as "Status", '2TC' as "Source" from TC left join SP on TC."code" = SP."code" where (SP."code" is null or SP."Status" <> 2) ) order by "Source" asc, "Code" asc

I thought I could replace my join in the second query with a result set, but I can't seem to get that to work with DBISAM.

Any advice?

Thanks

This approach occurred to me, but doesn't include a join. Will I be hit by any performance issues because of this? Is there a way to do it with a Join in DBISAM?

(Select "Code", "Status", '1SP' as "Source" from SP where "Status" = 2 group by "Code")
union all
(Select distinct "Code", 0 as "Status", '2TC' as "Source" from TC
where TC."Code" not in (select "code" from SP where "Status" = 2)) order by "Source" asc,"Code" asc

Thanks

JSherman wrote:

I have two tables. The first table is a list of codes. When one of these codes is used a record is created in a second table with a status next to it. A status of 0 means it has been added and is being considered, a status of 1 means it has been cancelled, and status of 2 means it was actually used. I want to write a query to get a list of codes. The codes in table 2 that have a status of 2 I want at the top of my result set. All the rest of the codes I want to be after that list.

Table 1 (TC)
Code
--------
100
200
300
400
500

Table 2 (SP)
Code          Status
---------------------------
100             0
200             2
200             1
400             2
400             2
500             1
500             2

The result would look like this:
Code            Status
-----------------------------
200               2
400               2
500               2
100               0
300               0

Here is my first take at the sql. The problem is that if a code is in table one with a status other than 2 it won't show up in either list:
(Select "Code", "Status", '1SP' as "Source" from SP where "Status" = 2 group by "Code")
union all
(Select "Code", 0 as "Status", '2TC' as "Source" from TC left join SP on TC."code" = SP."code" where SP."code" is null)
order by "Source" asc, "Code" asc

To try to resolve it I added the or clause, but that just causes duplication of results in my second sql statement
(Select "Code",  "Status", '1SP' as "Source" from SP where "Status" = 2 group by "Code")
union all
(Select distinct "Code", 0 as "Status", '2TC' as "Source" from TC left join SP on TC."code" = SP."code" where (SP."code" is null or SP."Status" <> 2) ) order by "Source" asc, "Code" asc

I thought I could replace my join in the second query with a result set, but I can't seem to get that to work with DBISAM.

Any advice?

Thanks

On 1/17/2014 3:49 PM, JSherman wrote:
> I have two tables. The first table is a list of codes. When one of these codes is used a record is created in a second table with a status next to it. A status of 0 means it has been added and is being considered, a status of 1 means it has been cancelled, and status of 2 means it was actually used. I want to write a query to get a list of codes. The codes in table 2 that have a status of 2 I want at the top of my result set. All the rest of the codes I want to be after that list.

Unless there is something else that you need from table1 there is no
need to even bother with the join - table 2 already includes the code
which is the only column in table 1.

Simplest query one would be :

select * from table2 order by status desc

or if you don't want the duplicate 400 codes with status 2 then

select * from table2 group by code,status order by status desc

Raul

I do need data from Table 1. I want to list all the codes from Table 1. I just want to put all the codes from Table 1 that have a status of 2 in Table 2 at the top of my list.

Jared

The actual result would look like this:
Code            Status          Source
-------------------------------------------------
200               2                  1SP
400               2                  1SP
500               2                  1SP
100               0                  2TC
300               0                  2TC

That way you see that results are coming in from multiple sources.

<< I have two tables. The first table is a list of codes. When one of these
codes is used a record is created in a second table with a status next to
it. A status of 0 means it has been added and is being considered, a status
of 1 means it has been cancelled, and status of 2 means it was actually
used. I want to write a query to get a list of codes. The codes in table 2
that have a status of 2 I want at the top of my result set. All the rest of
the codes I want to be after that list. >>

Try something like this:

SELECT Code, Status, IF(Status=2,-1,Status) AS Rank
FROM Table1 TC INNER JOIN Table2 SP ON SP.Code=TC.Code
ORDER BY Rank, Code, Status,

Tim Young
Elevate Software
www.elevatesoft.com