Database

7-Nov-14 10:36

I understand that this is what I asked for, but I'm trying to figure out how to just get 1 record of each item, instead of the 2 when the Join is valid.

DECLARE @CategoryID INT;
SET @CategoryID = 23;

SELECT *
FROM ProductInfo p
LEFT JOIN ProductInfo_Inventory pi ON p.ProductID = pi.ProductID
WHERE p.Category = @CategoryID

[SORT OF SOLVED]

jkirkerx7-Nov-14 11:17

Re: Left Join and double results where the join is complete

7-Nov-14 11:17

I think I get it now.
If I were to request a single record, then the join would be fine.

But since I requesting all records that match the category ID, you get what you ask for.

So I decided to just make a separate call back to the database for the inventory data as I loop through the presentation aspect of the program.

rjdudley24-Nov-14 14:02

rjdudley

24-Nov-14 14:02

Don't select *, just select the columns you need, and you can probably use DISTINCT to filter the list down to a unique set.

Re: Left Join and double results where the join is complete

jkirkerx25-Nov-14 7:39

Re: Left Join and double results where the join is complete

25-Nov-14 7:39

I ended up wrapping the select in a select statement, and adding the union to get the history and the current records. I think I went through like 5 or 6 versions of this before I got it to work. The numbers they produce are correct, so perhaps I got it right, and it's fast.

"SELECT " & _
"  SUM(FSHIPQTY * FCOST) " & _
", SUM(FSHIPQTY * FPRICE) " & _
", SUM(FAMOUNT) " & _
", SUM(FSHIPQTY * FPRICE - FSHIPQTY * FCOST) " & _
" FROM " & _
"( " & _
" SELECT " & _
"     h.FSHIPQTY " & _
"   , h.FCOST " & _
"   , h.FPRICE " & _
"   , h.FAMOUNT " & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" AND " & _
" h.FITEMNO = @FITEMNO " & _
" UNION  ALL " & _
" SELECT " & _
"     v.FSHIPQTY " & _
"   , v.FCOST " & _
"   , v.FPRICE " & _
"   , v.FAMOUNT " & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" AND " & _
" v.FITEMNO = @FITEMNO " & _
") "

rjdudley25-Nov-14 8:24

rjdudley

25-Nov-14 8:24

What you've done there is open to SQL Injection attackes, which are a serious security issue. For a really long but excellent article about dynamic SQL and SQL injection, see http://www.sommarskog.se/dynamic_sql.html[^].

A nested query like this will work for a small dataset, but the inner queries will be run for every row in the outer query. It won't take long for this to be a very slow query overall.

Re: Left Join and double results where the join is complete

jkirkerx25-Nov-14 9:28

what is the equivalent linq query for the following sql query?

25-Nov-14 9:28

It's an old DOS accounting program "Account Mate" using DBF files; Fox Pro; in which a Windows 7 and 8 application that I wrote for the customer provides extra features used all day long for electronic invoicing, electronic order confirmation, electronic past due statements via emails and PDF attachments.

It's not exposed to the internet.

Dhyanga7-Nov-14 5:42

Re: what is the equivalent linq query for the following sql query?

7-Nov-14 5:42

string qryInterviews = "SELECT ID, UpdatedByAdmin, UpdatedBy,Deleted " +
"CASE UpdatedByAdmin WHEN 1 THEN Date1 ELSE TimesForDate1 END AS D1, " +
"CASE UpdatedByAdmin WHEN 1 THEN Date2 ELSE TimesForDate2 END AS D2, " +
"CASE UpdatedByAdmin WHEN 1 THEN Date3 ELSE TimesForDate3 END AS D3, " +

"FROM myTable  where (Deleted is null OR Deleted = 0)";

In the above query, UpdatedByAdmin,Deleted are of tinyInt datatype. Please help.

Dhyanga

PIEBALDconsult7-Nov-14 5:52

PIEBALDconsult

7-Nov-14 5:52

That doesn't even look like valid SQL.

Re: what is the equivalent linq query for the following sql query?

Dhyanga7-Nov-14 5:57

Re: what is the equivalent linq query for the following sql query?

7-Nov-14 5:57

this is the currently running query in my project. I don't know how columnname with tinyInt datatype be compared to any given value in the case statement in linq.

Dhyanga

Dhyanga7-Nov-14 6:03

Re: what is the equivalent linq query for the following sql query?

7-Nov-14 6:03

var query = (from sub in db.myTable
                            where  (sub.Deleted == null || sub.Deleted == 0)
                            select new
                            {
                                sub.ID,
                                sub.UpdatedByAdmin,
                                sub.UpdatedBy,
                                sub.Deleted,
                                D1 = sub.UpdatedByAdmin.Equals(true ? sub.Date1 : sub.TimesForDate1),
                                D2 = sub.UpdatedByAdmin.Equals(true ? sub.Date2 : sub.TimesForDate2),
                                D3 = sub.UpdatedByAdmin.Equals(true ? sub.Date3 : sub.TimesForDate3)


                            }).ToList();

But this is giving error message as:

System.ArgumentException: DbComparisonExpression requires arguments with comparable types.

I think its because my UpdatedByAdmin, Deleted columns are tinyInt datatype in the database. But When i used these fields in model class, i used byte datatype. I thought byte is equivalent to tinyInt.
Please help.

Dhyanga

PIEBALDconsult7-Nov-14 6:40

PIEBALDconsult

7-Nov-14 6:40

Dhyanga wrote:
I thought byte is equivalent to tinyInt.

It is, but SQL tends to be more forgiving than C#.

Re: what is the equivalent linq query for the following sql query?

Eddy Vluggen7-Nov-14 7:31

Eddy Vluggen

7-Nov-14 7:31

In this case, the query is more readable than the LINQ version. Secondly, this query executes on the server.

What use does changing it to LINQ have?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: what is the equivalent linq query for the following sql query?

Dhyanga7-Nov-14 7:44

migrate a database in informix server to a local server mysql or postgree

7-Nov-14 7:44

I am trying to implement entity framework to that project..

Dhyanga

Ibrahim.elh6-Nov-14 3:48

Ibrahim.elh

6-Nov-14 3:48

hello,

I'm looking for a tool to migrate a database in informix server to a local server mysql or postgree.

what you proposed?
Thank you.

Re: migrate a database in informix server to a local server mysql or postgree

Eddy Vluggen7-Nov-14 7:33

Eddy Vluggen

7-Nov-14 7:33

Ibrahim.elh wrote:
what you proposed?

Create a small application, connect to the source-database, connect to the target-database, read a record from source, write a record to target.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

SQL Left Outer Join Problem

Vimalsoft(Pty) Ltd4-Nov-14 21:53

Vimalsoft(Pty) Ltd

4-Nov-14 21:53

i have Join that i expect to give me 3 Records but it gives me to Records . There is a Record in Table A and in table "C" there are two records , i used a left outer Join and i only get two records instead of 3. i dont want to use a Union to do this. Below is an Example

SQL

CREATE TABLE #TABLEA 
(
LIS_KEY VARCHAR(MAX) NULL,
FUNC_KEY VARCHAR(MAX) NULL  
)

INSERT INTO #TABLEA 
VALUES('047600055/R',NULL)


CREATE TABLE #TABLEC 
(
LIS_KEY VARCHAR(MAX) NULL,
FUNC_KEY VARCHAR(MAX) NULL  
)


INSERT INTO #TABLEC 
VALUES('047600055/R','GEOSS001')


INSERT INTO #TABLEC 
VALUES('047600055/R','GEOSS002')

SELECT * FROM   #TABLEA A 
left outer  JOIN   #TABLEC C 
ON  A.LIS_KEY  =  C.LIS_KEY     
WHERE A.LIS_KEY  = '047600055/R'

Thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com

Vimalsoft(Pty) Ltd4-Nov-14 22:11

Vimalsoft(Pty) Ltd

4-Nov-14 22:11

Got the Solution

SQL

SELECT *
FROM
(
SELECT * FROM   #TABLEA A 
UNION ALL
SELECT * FROM   #TABLEC C 
)t   
WHERE LIS_KEY  = '047600055/R'

Mycroft Holmes4-Nov-14 22:33

Mycroft Holmes

4-Nov-14 22:33

What happened to I don't want to use UNION Laugh | :laugh:

Never underestimate the power of human stupidity
RAH

Vimalsoft(Pty) Ltd4-Nov-14 22:34

Vimalsoft(Pty) Ltd

4-Nov-14 22:34

i take back my words Laugh | :laugh: