Can someone tell me options to do this statment because this one does
not work!
SELECT Name FROM tbl_J
WHERE J_ID IN
(SELECT J1, J2, J3, J4, J5, J6
FROM tbl_CJ
WHERE CJ_ID =23515) ORDER BY Name 3 1913
On Mar 19, 8:39 am, "Giorgio" <FJMarti...@goo glemail.comwrot e:
Can someone tell me options to do this statment because this one does
not work!
SELECT Name FROM tbl_J
WHERE J_ID IN
(SELECT J1, J2, J3, J4, J5, J6
FROM tbl_CJ
WHERE CJ_ID =23515) ORDER BY Name
I won't go into why this suggests a problem with your model, but I
think you want something like this:
SELECT Name FROM tbl_J
WHERE J_ID IN
(SELECT J1
FROM tbl_CJ
WHERE CJ_ID =23515
UNION
SELECT J2
FROM tbl_CJ
WHERE CJ_ID =23515
....
UNION
SELECT J6
FROM tbl_CJ
WHERE CJ_ID =23515) ORDER BY Name
Alternatively you could OR them all together:
SELECT Name FROM tbl_J
WHERE J_ID IN
(SELECT J1
FROM tbl_CJ
WHERE CJ_ID =23515)
OR J_ID IN
(SELECT J1
FROM tbl_CJ
WHERE CJ_ID =23515)
....
OR J_ID IN
(SELECT J6
FROM tbl_CJ
WHERE CJ_ID =23515)
ORDER BY Name
Giorgio wrote:
Can someone tell me options to do this statment because this one does
not work!
SELECT Name FROM tbl_J
WHERE J_ID IN
(SELECT J1, J2, J3, J4, J5, J6
FROM tbl_CJ
WHERE CJ_ID =23515) ORDER BY Name
You probably want to re-design tbl_CJ from this:
CJ_ID | J1 | J2 | J3 | J4 | J5 | J6
------+----+----+----+----+----+----
23515 | 1 | 2 | 3 | 4 |null|null
23516 | 5 | 6 | 7 |null|null|null
to this:
CJ_ID | J_ID
------+-----
23515 | 1
23515 | 2
23515 | 3
23515 | 4
23516 | 5
23516 | 6
23516 | 7
in which case the query becomes simple:
select j.Name
from tbl_J j
join tbl_CJ cj on cj.J_ID = j.J_ID
where cj.CJ_ID = 23515
order by j.Name
and, as an extra added bonus, you are no longer limited to a maximum of
six tbl_J records per tbl_CJ record.
Failing that, here is one of several ways to do it:
select Name
from tbl_J
where J_ID in (select J1 from tbl_CJ where CJ_ID = 23515)
or J_ID in (select J2 from tbl_CJ where CJ_ID = 23515)
or J_ID in (select J3 from tbl_CJ where CJ_ID = 23515)
or J_ID in (select J4 from tbl_CJ where CJ_ID = 23515)
or J_ID in (select J5 from tbl_CJ where CJ_ID = 23515)
or J_ID in (select J6 from tbl_CJ where CJ_ID = 23515)
order by Name
I posted this in microsoft.publi c.sqlserver.pro gramming in response to
the copy you posted there. In the future if you must post to multiple
groups, include all of them in the same copy of the message.
There are a number of ways to do this, but most become long and
complicated. This may be the simplest.
SELECT Name
FROM tbl_J as A
WHERE EXISTS
(SELECT * FROM tbl_CJ as B
WHERE B.CJ_ID = 23515
AND A.J_ID IN
(B.J1, B.J2, B.J3,
B.J4, B.J5, B.J6))
ORDER BY Name
Roy Harvey
Beacon Falls, CT
On 19 Mar 2007 05:39:37 -0700, "Giorgio" <FJ********@goo glemail.com>
wrote:
>Can someone tell me options to do this statment because this one does not work!
SELECT Name FROM tbl_J WHERE J_ID IN (SELECT J1, J2, J3, J4, J5, J6 FROM tbl_CJ WHERE CJ_ID =23515) ORDER BY Name
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: netpurpose |
last post by:
I need to extract data from this table to find the lowest prices of
each product as of today. The product will be listed/grouped by the
name only, discarding the product code - I use SUBSTRING(ProductName,
1, CHARINDEX('(', ProductName)-2).
I can get this result, but I had to use several views (totally
inefficient). I think this can be done in one efficient/fast query,
but I can't think of one.
In the case that one query is not...
|
by: stu_gots |
last post by:
I have been losing sleep over this puzzle, and I'm convinced my train
of thought is heading in the wrong direction. It is difficult to
explain my circumstances, so I will present an identical make-believe
challenge in order to avoid confusing the issue further.
Suppose I was hosting a dinner and I wanted to invite exactly 12 guests
from my neighborhood. I'm really picky about that... I have 12 chairs
besides my own, and I want them all...
|
by: x muzuo |
last post by:
Hi guys,
I have got a prob of javascript form validation which just doesnt work
with my ASP code. Can any one help me out please.
Here is the code:
{////<<head>
<title>IIBO Submit Page</title>
</head>
<style type="text/css">
|
by: Craig Keightley |
last post by:
Please help, i have attached my page which worksin IE but i cannnot get the
drop down menu to fucntion in firefox. Any one have any ideas why?
Many Thanks
Craig
<<<<<<<<<<<<<<CODE>>>>>>>>>>>>>>>>
<html>
|
by: Pat Patterson |
last post by:
I'm having serious issues with a page I'm developing. I just need some
simple help, and was hoping someone might be able to help me out in
here.
I have a form, that consists of 3 pages of fields. I'd like to create
a page in which all of this is stored as you move along as hidden
variables, until the end, when the user submits. I can't figure out
one thing: I have dynamic form elements (dropdowns), that I'd like to
use instead of...
| |
by: JonathanParker |
last post by:
Hello,
Wondered if you could help me with a little issue I'm having. I'm exporting some data from Access to Excel and converting into some fancy graphs. The number of series' ranges from 2 to 5. Below isa copy of the code I am using to format the graph in terms of series names, layout, colours of lines, etc. When the full 5 series are there to be charted there is no problem with the code, however, when I try to convert less the 5 series I...
|
by: casper christensen |
last post by:
Hi
I run a directory, where programs are listed based on the number of
clicks they have recieved. The program with most clicks are placed on
top and so on. Now I would like people to be apple to place a link on
there site so people can vote for their program, "ad a click". eg
someone clicks the link on some page and it counts +1 click on my page.
if some one clicks the link below it will count a click on my page.
|
by: Debbiedo |
last post by:
My software program outputs an XML Driving Directions file that I need
to input into an Access table (although if need be I can import a dbf
or xls) so that I can relate one of the fields (fromStop) and its
associated driving directions back to a relational database. I have
asked my software vendor for solutions but thus far they have not come
up with anything. I am totally unfamiliar with XML so I am struggling
with how to do this. I have...
|
by: upstart |
last post by:
Hi everyone…this is a tough one.
You guys have been such a help before, hopefully you can point me in the right direction now.
I have a Report I am working on that uses a stored procedure to pass along all off the parameters to the reporting generator (Crystal Reports) and I was currently trying to optimize it for my users, but am not sure of the best way to go about doing it. It took me forever to get it to work as it is now.
I...
|
by: thuythu |
last post by:
Please help me....
I used and Javascript to view the data. But when i click button open a popup windows, then select data and click save button. The popup close and return the main page, but the textbox value in the main page is undefined
----------------------------------------
here are code main page:
-------------------------------------------
<script language="JavaScript">
var thedata;
var newwin;
var thenumber;
function...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |